﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
	public class Supervise
	{
		/// <summary>
		/// 批量删除消息
		/// </summary>
		/// <param name="message"></param>
		/// <returns></returns>
		public static int DeleteMessage_Batch(IList<Message> message)
		{
			int flag = 0;
			HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
			try
			{
				h.BeginTrans();
				foreach (var item in message)
				{
					h.ExecuteNonQuery(string.Format(@"DELETE FROM  [dbo].[TB_Message]
                WHERE MessageID= '{0}'", item.MessageID));
				}
				h.CommitTrans();
				flag = 1;
			}
			catch (Exception ee)
			{
				h.RollTrans();
				//HZ.Common.log4netManager.LogDebug("DAL_Supervise", ee.Message, ee);
				flag = 0;
			}
			return flag;
		}
		/// <summary>
		/// 更新消息
		/// </summary>
		/// <param name="message"></param>
		/// <returns></returns>
		public static int UpdateMessage(Message message)
		{
			return DAL_Helper.OperateTableSQL<Message>(message, TableOperator.Update);
		}
		/// <summary>
		/// 批量添加消息
		/// </summary>
		/// <param name="message"></param>
		/// <returns></returns>
		public static int AddMessage_Batch(IList<Message> message)
		{
			int flag = 0;
			HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
			try
			{
				h.BeginTrans();
				foreach (var item in message)
				{
					item.flag = 0;
					h.ExecuteNonQuery(DAL_Helper.GetInsertSql(item));
				}
				h.CommitTrans();
				flag = 1;
			}
			catch (Exception ee)
			{
				h.RollTrans();
				//HZ.Common.log4netManager.LogDebug("DAL_Supervise", ee.Message, ee);
				flag = 0;
			}
			return flag;
		}

		/// <summary>
		/// 获取已经发送的消息
		/// (机构ID无需传入)
		/// </summary> 
		public static IList<Model.View.Message> GetMessageSent(Model.Query.QMessage where, int currentpage, int pagesize, out int totalrecord)
		{
			//where.Append = string.Format("Sender = '{0}' AND flag=1 ", p);
			where.Append = " flag=1 ";
			return DAL_Helper.GetPagedView<Model.View.Message, Model.Query.QMessage>(where, currentpage, pagesize, out totalrecord);
		}
		/// <summary>
		/// 条件查询消息[已收信息]
		/// </summary> 
		public static IList<Model.View.Message> GetMessage(Model.Query.QMessage where, int currentpage, int pagesize, out int totalrecord)
		{
			//where.Append = string.Format("Reciever = '{0}' AND flag=0 ", p);
			where.Append = " flag=0 ";
			return DAL_Helper.GetPagedView<Model.View.Message, Model.Query.QMessage>(where, currentpage, pagesize, out totalrecord);
		}
		/// <summary>
		/// 获取待审核的通知公告
		/// </summary> 
		public static IList<Model.View.Notice> GetAuditNotice(Model.Query.QNotice where, int currentpage, int pagesize, out int totalrecord)
		{
			string strwhere = DAL_Helper.GetWhereCondition(where);
			string sql = string.Format(@"SELECT distinct SenderName,
                                            RoleNames,
                                            NoticeSerialID,
                                            Sender,
                                            SendTime,
                                            Title,
                                            NoticeContent,
                                            AuditStatus,
                                            NoticeType,
                                            AuditUser,
                                            PostUser,'' AS Reciever
                                            FROM dbo.View_Notice where 1=1 {0}", strwhere);
			HZ.Data.PageDescribeYn des = new HZ.Data.PageDescribeYn(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return DAL_Helper.CommonFillList<Model.View.Notice>(des.GetSpecailPage());
		}
		/// <summary>
		/// 日志管理---日志查询
		/// </summary> 
		public static DataTable GetLog(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string SQL = String.Format(@" SELECT * FROM  TB_KeyOperationLogJG WHERE 1=1 {0}", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(SQL, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			DataTable dt = pdes.GetSpecailPage();
			return dt;
		}

		public static DataTable GetDT()
		{
			string sql = "SELECT * FROM View_Supervise";
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}

		/// <summary>
		/// 获取图标汇总个数
		/// </summary>
		/// <returns></returns>
		public static DataTable GetSuperviseChart1(object sqlwhere)
		{
			StringBuilder sql = new StringBuilder();

			sql.Append("select COUNT(distinct(HospitalID)) AS HospitalCount,COUNT(distinct(ProcureCatalogID)) AS ProcureCatalogCount,");
			sql.Append("COUNT(distinct(CompanyID_SC)) AS CompanySCCount,COUNT(distinct(CompanyID_PS)) AS CompanyPSCount ");
			sql.Append(" from CACHE_STAT_Goods  where 1=1 ");



			string where = HZ.Data.DAL_Helper.GetWhereCondition(sqlwhere, false);

			if (!string.IsNullOrEmpty(where))
			{
				sql.AppendFormat(" {0} ", where);
			}

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql.ToString());
		}
		/// <summary>
		/// 产品配送率
		/// 条件修改    modify  by张雄  2011.5.5 //i=0 表示地区分开的数据信息 ，i=1 表示不分地区汇总后的数据
		/// </summary>
		/// <param name="proce"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable getGoodsPSLList(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			string sqlWhere = "AND areaid LIKE '22%'";
			if (i == 0)//i=0 表示地区分开的数据信息 ，i=1 表示不分地区汇总后的数据
			{
				sql = @"select * from TB_JG_GoodsHuiZong_PSL  where 1=1 " + where + sqlWhere + " order by " + ordrby;
			}
			else
			{
				sql = @" 
                        select distinct productname,MedicineModelName,outlookc,unit,pack,CompanyName_SC,BidPrice,a.*,
                        case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 
                        when a.DistributeAmountTotal/a.PurchaseAmountTotal >1 then 1 
                        else a.DistributeAmountTotal/a.PurchaseAmountTotal end as DistributeRate,
                        case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 
                        when a.WareHouseAmountTotal/a.PurchaseAmountTotal>1 then 1 
                        else a.WareHouseAmountTotal/a.PurchaseAmountTotal end as WareHouseRate 
                        from dbo.TB_JG_GoodsHuiZong_PSL  join (
                        select ProcureCatalogID,sum(PurchaseAmountTotal) as PurchaseAmountTotal,
                        sum(DistributeAmountTotal) as DistributeAmountTotal,sum(WareHouseAmountTotal) as WareHouseAmountTotal  
                        from TB_JG_GoodsHuiZong_PSL  where 1=1 " + where + "group by  ProcureCatalogID) a on a.ProcureCatalogID=TB_JG_GoodsHuiZong_PSL.ProcureCatalogID where 1=1" + where + " order by " + ordrby;

			}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 医院药品汇总     //  
		/// JOHN
		/// </summary>
		/// <param name="i">IsCountry表示是否基层医疗机构：0 非基层医疗机构，1乡镇，2社区，3 民营，4 其他</param>
		/// <param name="where"></param>
		/// <param name="ordrby"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static System.Data.DataTable GetHospitalGoodsList(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";

			if (i == 5)//IsCountry表示是否基层医疗机构：0 非基层医疗机构，1乡镇，2社区，3 民营，4 其他
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal, 
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}
                        ", where, ordrby);
			}
			else if (i == 0)
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1 AND Levels IN ('3','4','5','6')
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}                                            
                                            ", where, ordrby);
			}
			else if (i == 1)
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal, 
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1 AND IsCountry = 1
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}                                            
                                            ", where, ordrby);
			}
			else if (i == 2)
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal, 
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1 AND IsCountry =2
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}                                            
                                            ", where, ordrby);
			}
			else if (i == 3)
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal, 
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1 AND IsCountry IN ('1','2','3','4')
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}                                            
                                            ", where, ordrby);
			}
			else if (i == 4)
			{
				sql = string.Format(@"
                            SELECT  AreaName,
                                    HospitalName,
                                    FatherAreaName,
                                    B.HospitalID,
                                    PurchaseAmountTotal,
                                    DistributeRate,
                                    WareHouseRate,
                                    WareHouseAmountTotal,
                                    DistributeAmountTotal,
                                    DecreasingAmplitudeBatch,
                                    SellCount,
                                    SellAmountTotal,
                                    SellRate
                            FROM    View_HospitalArea
                                    INNER JOIN ( SELECT HospitalID,
                                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                             WHEN SUM(PurchaseCount) = 0 THEN 0
                                                             ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                     * Price))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        END AS DistributeRate,
                                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal, 
                                                        Case when sum(WareHouseCount) = 0 then 0
                                                             when sum(PurchaseCount) = 0 then 0
                                                             else CONVERT(float, SUM(WarehouseAmount))
                                                                  / SUM(isnull(PurchaseAmount, 0))
                                                        end AS WareHouseRate,
                                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal,
                                                        CASE WHEN SUM(LimitPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             WHEN SUM(SellPrice * PurchaseCount) = 0
                                                             THEN 0
                                                             ELSE ( SUM(LimitPrice * PurchaseCount)
                                                                    - SUM(SellPrice * PurchaseCount) )
                                                                  / SUM(isnull(LimitPrice * PurchaseCount,
                                                                               0))
                                                        END AS DecreasingAmplitudeBatch,
                                                       COUNT(ProcureCatalogID) proCount ,
                                                       COUNT(SellCount) SellCount,
                                                       SUM(ISNULL(Dis_SellPrice * SellCount,0)) SellAmountTotal,
                                                       
                                                       CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                                       WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1
                                                       ELSE (SUM(Dis_SellPrice * SellCount)/SUM(ISNULL(WarehouseAmount,0))) END SellRate
                                                 FROM   View_Supervise
                                                 WHERE  1 = 1 AND IsCountry = 4
                                                        {0}
                                                 GROUP BY HospitalID
                                               ) AS B ON View_HospitalArea.HospitalID = B.HospitalID
                                              ORDER BY {1}                                            
                                            ", where, ordrby);
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 医院药品汇总     //  
		/// JOHN
		/// </summary>
		/// <param name="i"></param>
		/// <param name="where"></param>
		/// <param name="ordrby"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static System.Data.DataTable GetHospitalGoods(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";

			if (i == 5)//i=0 表示地区分开的数据信息 ，i=1 表示不分地区汇总后的数据
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                        SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                                Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                         WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                         ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                                 * Price))
                                                                              / SUM(isnull(PurchaseAmount, 0))
                                                                    END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}
			else if (i == 0)
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                         SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                                Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                         WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                         ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                                 * Price))
                                                                              / SUM(isnull(PurchaseAmount, 0))
                                                                    END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 AND Levels IN ('3','4','5','6')  {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}
			else if (i == 1)
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                         SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                            Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                             * Price))
                                                                          / SUM(isnull(PurchaseAmount, 0))
                                                                END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 AND IsCountry = 1  {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}
			else if (i == 2)
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                         SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                                Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                         WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                         ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                                 * Price))
                                                                              / SUM(isnull(PurchaseAmount, 0))
                                                                    END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 AND IsCountry = 2  {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}
			else if (i == 3)
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                         SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                        Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                 WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                         * Price))
                                                                      / SUM(isnull(PurchaseAmount, 0))
                                                            END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 AND IsCountry IN ('1','2','3','4')  {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}
			else if (i == 4)
			{
				sql = string.Format(@"
                                        SELECT  MAX(ProcureCatalogID) ProcureCatalogID,
                                        MAX(ProductName) ProductName,
                                        MAX(MedicineModelName) MedicineModelName,
                                        MAX(Pack) Pack,
                                        MAX(Unit) Unit,
                                        HospitalName,
                                        MAX(Price) Price,
                                        Case when sum(WareHouseCount) = 0 then 0
                                             when sum(PurchaseCount) = 0 then 0
                                             else CONVERT(float, SUM(WarehouseAmount))
                                                  / SUM(isnull(PurchaseAmount, 0))
                                        end AS WareHouseRate,
                                        SUM(ISNULL(WarehouseAmount,0)) AS WareHouseAmountTotal, 
                                         SUM(ISNULL(PurchaseAmount,0)) PurchaseAmountTotal, 
                                         SUM(ISNULL(DistributeCount * Price,0)) AS DistributeAmountTotal, 
                                            Case WHEN SUM(DistributeCount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                             * Price))
                                                                          / SUM(isnull(PurchaseAmount, 0))
                                                                END AS DistributeRate,
                                        COUNT(SellCount) SellCount,
                                        SUM(ISNULL(Dis_SellPrice * SellCount, 0)) SellAmountTotal,
                                        CASE WHEN SUM(Dis_SellPrice * SellCount) = 0 THEN 0
                                             WHEN SUM(WarehouseAmount) = 0 THEN 0
WHEN CONVERT(decimal(18,3),CONVERT(float, SUM(Dis_SellPrice * SellCount)) / SUM(ISNULL(WarehouseAmount, 0)))>1 then 1  
                                             ELSE ( SUM(Dis_SellPrice * SellCount)
                                                    / SUM(ISNULL(WarehouseAmount, 0)) )
                                        END SellRate
                                FROM    View_Supervise
                                WHERE   1 = 1 AND IsCountry = 4 {0}
                                GROUP BY ProcureCatalogID,
                                        HospitalName
                                ORDER BY {1}
                  
                        ", where, ordrby);
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 按照药品统计    by张雄
		/// </summary>
		/// <param name="currentpage">页数</param>
		/// <param name="pagesize">每页的行数</param>
		/// <param name="totalrecord"></param>
		/// <returns>药品统计的绑定源</returns>
		public static DataTable GetGoodsInfoLList(string where, string whereH, string whereP, int currentpage, int pagesize, out int totalrecord)
		{

			string sql = string.Format(@" select row_number() over (order by 入库金额/采购金额  asc, 配送金额/采购金额  asc,流水号 asc) 序号,*,
            配送金额/采购金额 [配送率(配送金额/采购金额)],入库金额/采购金额 [入库率(入库金额/采购金额)] from (

            select a.procurecatalogid 流水号,productname 通用名,medicinemodelname 剂型,companyname_sc 生产企业,
            pack 采购规格,unit 采购单位,price 采购单价, purchasecount 采购数量, purchaseamount 采购金额,
            isnull(distributecount,0) 配送数量,price*isnull(distributecount,0) 配送金额, isnull(warehousecount,0) 入库数量,
            price*isnull(warehousecount,0) 入库金额 from (select procurecatalogid,max(productname) productname,
            max(medicinemodelname) medicinemodelname,max(companyname_sc) companyname_sc,max(pack) pack,max(unit) unit,
            max(price) price,sum( purchasecount) purchasecount,sum(price*purchasecount) purchaseamount   from dbo.view_purchaseorderdetail 
            where 1=1 and orderdetailstate>0 {0} group by procurecatalogid

            ) a left join (

            select procurecatalogid,isnull(sum(distributecount),0) distributecount,isnull(sum(warehousecount),0) warehousecount 
            from tb_distribution where 1=1 {2}{1} group by procurecatalogid

            ) b on a.procurecatalogid=b.procurecatalogid)t order by 入库金额/采购金额  asc, 配送金额/采购金额  asc ,流水号 asc ", where, whereH, whereP);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 配送企业统计 by张雄
		/// </summary>
		/// <param name="currentpage">当前的页数</param>
		/// <param name="pagesize">每页的行数</param>
		/// <param name="totalrecord">参数</param>
		/// <returns>配送企业统计数据源</returns>
		public static DataTable GetCompanyInfo_PS(string str, string whereH, string whereP, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = string.Format(@"  select row_number() over (order by 入库金额/采购金额  asc, 配送金额/采购金额  asc ,企业号 asc) 序号,*,
                                            配送金额/采购金额 [配送率(配送金额/采购金额)],入库金额/采购金额 [入库率(入库金额/采购金额)] from (

                                             select a.companyid_ps  企业号, companyname_ps 配送企业 ,isnull(purchaseamount,0) 采购金额, 
                                            isnull(distributeamount,0) 配送金额, isnull(warehouseamount,0) 入库金额 from (

                                            select companyid_ps ,max(companyname_ps) companyname_ps ,sum(price*purchasecount) purchaseamount from dbo.view_purchaseorderdetail
                                             where orderdetailstate>0 {0} group by companyid_ps  
                                            ) a left join (
                                             select companyid_ps,isnull(sum(price*distributecount),0) distributeamount,isnull(sum(price*warehousecount),0) warehouseamount from dbo.tb_distribution
                                             where 1=1 {1}{2}  group by companyid_ps ) b on a.companyid_ps=b.companyid_ps  )t order by  入库金额/采购金额  asc, 配送金额/采购金额  asc ,企业号 asc
                                       ", str, whereH, whereP);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 生产企业统计 by张雄
		/// </summary>
		/// <param name="currentpage">当前的页数</param>
		/// <param name="pagesize">每页的行数</param>
		/// <param name="totalrecord">参数</param>
		/// <returns>生产企业统计数据源</returns>
		public static DataTable GetCompanyInfo_SC(string str, string whereH, string whereP, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = string.Format(@"   select row_number() over (order by 入库金额/采购金额  asc, 配送金额/采购金额  asc ,企业号 asc) 序号,*,
                                            配送金额/采购金额 [配送率(配送金额/采购金额)],入库金额/采购金额 [入库率(入库金额/采购金额)] from (

                                             select a.companyid_sc  企业号, companyname_sc 生产企业 ,isnull(purchaseamount,0) 采购金额, 
                                            isnull(distributeamount,0) 配送金额, isnull(warehouseamount,0) 入库金额 from (

                                            select companyid_sc ,max(companyname_sc) companyname_sc ,sum(price*purchasecount) purchaseamount from dbo.view_purchaseorderdetail
                                             where orderdetailstate>0 {0} group by companyid_sc 
                                            
                                            ) a left join (

                                             select companyid_sc,isnull(sum(price*dis_distributecount),0) distributeamount,
                                             isnull(sum(price*dis_warehousecount),0) warehouseamount from view_distribution
                                             where 1=1 {1}{2} group by companyid_sc 
                                                
                                            ) b on a.companyid_sc=b.companyid_sc )t  order by  入库金额/采购金额  asc, 配送金额/采购金额  asc ,企业号 asc
                                        ", str, whereH, whereP);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 按地区统计 by张雄
		/// </summary>
		/// <param name="currentpage">当前的页数</param>
		/// <param name="pagesize">每页的行数</param>
		/// <param name="totalrecord">参数</param>
		/// <returns>按地区统计数据源</returns>
		public static DataTable GetAreaInfoList(string str, string whereH, string whereP, int currentpage, int pagesize, out int totalrecord)
		{
			string str1 = "AND AreaID LIKE '22%'";
			string sql = string.Format(@"   select row_number() over (order by 入库金额/采购金额  asc, 配送金额/采购金额  asc ,地区名称 asc) 序号,*,
                                            配送金额/采购金额 [配送率(配送金额/采购金额)],入库金额/采购金额 [入库率(入库金额/采购金额)] from (
                                             select a.areaname  地区名称,isnull(purchaseamount,0) 采购金额, isnull(distributeamount,0) 配送金额, isnull(warehouseamount,0) 入库金额 from 
                                            (select areaid ,max(areaname) areaname ,sum(price*purchasecount) purchaseamount from dbo.view_purchaseorderdetail
                                             where orderdetailstate>0 {0} {3} group by areaid ) a  left join (
                                             select areaid,isnull(sum(price*dis_distributecount),0) distributeamount,isnull(sum(price*dis_warehousecount),0) warehouseamount from view_distribution
                                             where 1=1 {1}{2} group by areaid ) b on a.areaid=b.areaid )t
                                            order by  入库金额/采购金额  asc, 配送金额/采购金额  asc ,地区名称 asc
                                     ", str, whereH, whereP, str1);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 各县区配送率汇总
		/// </summary>
		/// <param name="hosproce"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable getAreaXianquList(string areaid, string orderby, string source, int currentpage, int pagesize, out int totalrecord)
		{

			StringBuilder sb = new StringBuilder();
			//if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			//{
			//    if (areaid.Length == 4)
			//    {
			//        sb.AppendFormat(" AND areaid like '{0}%'", areaid.Substring(0, 4));
			//    }
			//    else
			//    {
			//        sb.AppendFormat(" AND areaid ='{0}'", areaid);
			//    }
			//}
			//else
			//{
			//    sb.AppendFormat("AND areaid LIKE '{0}%'", areaid.Substring(0, 2));
			//}
			sb.Append(areaid);
			if (!string.IsNullOrEmpty(source) && source != "全部")
			{
				sb.AppendFormat("and sortsource = '" + source + "'");
			}
			string sql = @"select * from TB_JG_AreaXIANQU_PSL WHERE 1=1 " + sb.ToString() + " order by " + orderby;
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 获取图标汇总个数
		/// </summary>
		/// <returns></returns>
		public static DataTable GetSuperviseChart2(object sqlwhere)
		{
			StringBuilder sql = new StringBuilder();

			sql.Append("select COUNT(distinct(HospitalID)) AS HospitalCount,COUNT(distinct(ProcureCatalogID)) AS ProcureCatalogCount,");
			sql.Append("COUNT(distinct(CompanyID_SC)) AS CompanySCCount,COUNT(distinct(CompanyID_PS)) AS CompanyPSCount ");
			sql.Append("  from CACHE_STAT_Goods WHERE DistributeCount>0  ");

			string where = HZ.Data.DAL_Helper.GetWhereCondition(sqlwhere, false);

			if (!string.IsNullOrEmpty(where))
			{
				sql.AppendFormat(" {0} ", where);
			}



			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql.ToString());
		}

		public static DataTable GetSuperviseCompanySC_Chart()
		{
			StringBuilder sql = new StringBuilder();
			sql.Append("select COUNT(distinct ProcureCatalogId) AS ProcureCatalogIdTotal,");
			sql.Append("COUNT(distinct Sellname) AS SellnameTotal,COUNT(distinct BuyName) AS BuyNameTotal from TB_CompanyOrderDetail");

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql.ToString());
		}

		/// <summary>
		/// 修复所有月份
		/// </summary>
		/// <param name="dt">要补齐的table </param>
		/// <param name="flags">0 季月份 ，1 年月份，2 普通页面显示，补齐前12个月</param>
		/// <param name="statioryear">第几季或年，0 普通页面</param>
		/// <param name="monthcolunmname">月份字段名称</param>
		/// <returns></returns>
		public static DataTable FixAllMonth(DataTable dt, int flags, int statioryear, string monthcolunmname)
		{

			if (flags == 0) //补季度
			{
				if (dt.Rows.Count == 6 || dt.Rows.Count == 0) return dt;//有6列就直接返回；
				return Getstatics(dt, statioryear, monthcolunmname);
			}
			if (flags == 1)//补年度
			{
				if (dt.Rows.Count == 12 || dt.Rows.Count == 0) return dt;//有24列就直接返回；
				return GetsYearmonth(dt, statioryear, monthcolunmname);
			}
			if (flags == 2)//补前12个月
			{
				if (dt.Rows.Count == 0) return dt;//有24列就直接返回；
				return GetPageMonth(dt, monthcolunmname, 0, DateTime.Now, DateTime.Now);
			}
			return null;
		}
		/// <summary>
		/// 修复所选月份
		/// </summary>
		/// <param name="dt">>要补齐的table </param>
		/// <param name="Timeto">截至时间</param>
		/// <param name="Timefrom">开始时间</param>
		/// <param name="monthcolunmname">月份字段名称</param>
		/// <returns></returns>
		public static DataTable FixAllMonth(DataTable dt, DateTime Timeto, DateTime Timefrom, string monthcolunmname)
		{
			if (dt.Rows.Count == 0) return dt;//有24列就直接返回；
			return GetPageMonth(dt, monthcolunmname, 1, Timefrom, Timeto);
		}



		//补季度
		public static DataTable Getstatics(DataTable dt, int statioryear, string monthcolunmname)
		{

			int columnindex = dt.Columns.IndexOf(monthcolunmname);//获取月份字段在table 中的列 index
			int talbrowsindex = 0;//当前table 的行循环数；
			List<string> listde = GetMonthddictionary(statioryear);//返回所有月份所有类别
			foreach (string itme in listde)
			{
				if (dt.Rows.Count > talbrowsindex && itme.Equals(dt.Rows[talbrowsindex][columnindex].ToString() + "," + dt.Rows[talbrowsindex][columnindex + 1].ToString())) //如果相等月循环添加
				{
					talbrowsindex++;
				}
				else //不等说明缺，则添加
				{
					DataRow dw = dt.NewRow();
					dw[columnindex] = itme.Substring(0, 6);
					dw[columnindex + 1] = itme.Substring(7, 1);
					for (int ii = 0; ii < dt.Columns.Count; ii++)
					{
						if (ii == columnindex || ii == columnindex + 1) continue;
						dw[ii] = "0";
					}
					dt.Rows.Add(dw);
				}
			}
			var drs = dt.Select("", monthcolunmname + " asc ");
			DataTable stardt = dt.Clone();
			stardt = dt.Copy();
			stardt.Clear();
			foreach (var item in drs)
			{

				stardt.Rows.Add(item.ItemArray);
			}
			return stardt;
		}
		//返回所有月份所有类别
		private static List<string> GetMonthddictionary(int statioryear)
		{
			string strtimeYear = System.DateTime.Now.Year.ToString();
			int nmonth = 3 * statioryear - 2;//获取当前季度的第一个月份

			List<string> dd = new List<string>();
			for (int i = 0; i < 3; i++)
			{
				dd.Add(strtimeYear + AddZeromonth(nmonth + i) + ",0");
				dd.Add(strtimeYear + AddZeromonth(nmonth + i) + ",1");

			}
			return dd;
		}

		//补年度
		public static DataTable GetsYearmonth(DataTable dt, int statioryear, string monthcolunmname)
		{
			string strtimeYear = System.DateTime.Now.Year.ToString();
			int talbrowsindex = 0;//当前table 的行循环数；
			int columnindex = dt.Columns.IndexOf(monthcolunmname);//获取月份字段在table 中的列 index
			List<string> listde = GetMonthYear(statioryear.ToString());//返回所有月份所有类别
			foreach (string itme in listde)
			{
				if (dt.Rows.Count > talbrowsindex && itme.Equals(dt.Rows[talbrowsindex][columnindex].ToString() + "," + dt.Rows[talbrowsindex][columnindex + 1].ToString())) //如果相等月循环添加
				{
					talbrowsindex++;
				}
				else //不等说明缺，则添加
				{
					DataRow dw = dt.NewRow();
					dw[columnindex] = itme.Substring(0, 6);
					dw[columnindex + 1] = itme.Substring(7, 1);
					for (int ii = 0; ii < dt.Columns.Count; ii++)
					{
						if (ii == columnindex || ii == columnindex + 1) continue;
						dw[ii] = "0";
					}
					dt.Rows.Add(dw);
				}
			}
			var drs = dt.Select("", monthcolunmname + " asc ");
			DataTable stardt = dt.Clone();
			stardt = dt.Copy();
			stardt.Clear();
			foreach (var item in drs)
			{

				stardt.Rows.Add(item.ItemArray);
			}
			return stardt;

		}
		/// <summary>
		/// 各市配送率汇总
		/// modifyby张雄    2011.5.4
		/// </summary>
		/// <returns></returns>
		public static DataTable getAreaSHIlIST(string orderby, string Area, string source)
		{

			string strArea = "";
			if (!string.IsNullOrEmpty(Area) && Area != "全部")
			{
				strArea += "and areaname = '" + Area + "'";
			}
			if (!string.IsNullOrEmpty(source) && source != "全部")
			{
				strArea += "and sortsource = '" + source + "'";
			}
			string sql = "SELECT * FROM TB_JG_AreaSHI_PSL where 1=1" + strArea + " ORDER BY " + orderby;

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}

		//返回传入年份所有月份所有类别
		private static List<string> GetMonthYear(string year)
		{
			List<string> dd = new List<string>();
			for (int i = 1; i <= 12; i++)
			{
				dd.Add(year + AddZeromonth(i) + ",0");
				dd.Add(year + AddZeromonth(i) + ",1");
			}
			return dd;
		}
		//补当前页面的前12个月份所有类别
		public static DataTable GetPageMonth(DataTable dt, string monthcolunmname, int type, DateTime timefrom, DateTime timeto)
		{
			int talbrowsindex = 0;//当前table 的行循环数；
			int columnindex = dt.Columns.IndexOf(monthcolunmname);//获取月份字段在table 中的列 index
			List<string> listde;
			if (type == 0)
			{
				listde = GetPageMonthall();//返回所有月份所有类别
			}
			else
			{
				listde = GetPageMonthall(timefrom, timeto);//返回所有月份所有类别
			}

			foreach (string itme in listde)
			{
				if (dt.Rows.Count > talbrowsindex && itme.Equals(dt.Rows[talbrowsindex][columnindex].ToString() + "," + dt.Rows[talbrowsindex][columnindex + 1].ToString())) //如果相等月循环添加
				{
					talbrowsindex++;
				}
				else //不等说明缺，则添加
				{
					DataRow dw = dt.NewRow();
					dw[columnindex] = itme.Substring(0, 6);
					dw[columnindex + 1] = itme.Substring(7, 1);
					for (int ii = 0; ii < dt.Columns.Count; ii++)
					{
						if (ii == columnindex || ii == columnindex + 1) continue;
						dw[ii] = "0";
					}
					dt.Rows.Add(dw);
				}
			}
			var drs = dt.Select("", monthcolunmname + " asc ");
			DataTable stardt = dt.Clone();
			stardt = dt.Copy();
			stardt.Clear();
			foreach (var item in drs)
			{

				stardt.Rows.Add(item.ItemArray);
			}
			return stardt;
		}

		//返回 前12个月所有类别
		private static List<string> GetPageMonthall()
		{
			string year = System.DateTime.Now.Year.ToString(); //当前年份
			int nmonth = System.DateTime.Now.Month;//当前月份
			int nmm = 0;
			List<string> dd = new List<string>();
			for (int mm = nmonth; mm > 0; mm--)
			{
				dd.Add(year + AddZeromonth(mm) + ",0");
				dd.Add(year + AddZeromonth(mm) + ",1");
			}
			while (12 - nmm > nmonth)
			{
				dd.Add((Convert.ToInt32(year) - 1).ToString() + AddZeromonth(12 - nmm) + ",0");
				dd.Add((Convert.ToInt32(year) - 1).ToString() + AddZeromonth(12 - nmm) + ",1");
				nmm++;
			}
			dd.Sort();
			return dd;
		}
		//返回 所选时间所有类别
		private static List<string> GetPageMonthall(DateTime timefrom, DateTime timeto)
		{
			string year = timeto.Year.ToString(); //截至年份
			int nmonth = timeto.Month;//截至月份
			TimeSpan objT = timeto.Subtract(timefrom);//获得时间内一共多少时间
			int month2 = 0;

			if (timeto.Day == 1)
			{
				month2 = System.Convert.ToInt32(objT.Days / ((365 / 12))) + 1;
			}
			else
			{
				month2 = System.Convert.ToInt32(objT.Days / ((365 / 12))) + 2;
			}

			int nmm = 0;
			List<string> dd = new List<string>();
			for (int mm = nmonth; mm > 0; mm--)
			{
				if (timeto.Day == 1)
				{
					dd.Add(year + AddZeromonth(mm - 1) + ",0");
					dd.Add(year + AddZeromonth(mm - 1) + ",1");
				}
				else
				{
					dd.Add(year + AddZeromonth(mm) + ",0");
					dd.Add(year + AddZeromonth(mm) + ",1");
				}

				nmm++;
			}
			month2 = month2 - nmm;
			nmm = 0;
			int yuarTemp = 1;
			while (nmm < month2)
			{
				if (nmm < 12)
				{
					dd.Add((Convert.ToInt32(year) - yuarTemp).ToString() + AddZeromonth(12 - nmm) + ",0");
					dd.Add((Convert.ToInt32(year) - yuarTemp).ToString() + AddZeromonth(12 - nmm) + ",1");
					nmm++;
				}
				else
				{
					month2 = month2 - 12;
					nmm = 0;
					yuarTemp++;
					dd.Add((Convert.ToInt32(year) - yuarTemp).ToString() + AddZeromonth(12 - nmm) + ",0");
					dd.Add((Convert.ToInt32(year) - yuarTemp).ToString() + AddZeromonth(12 - nmm) + ",1");
					nmm++;
				}
			}
			dd.Sort();
			return dd;
		}
		/// <summary>
		/// 给月份补0
		/// </summary>
		/// <param name="month"></param>
		/// <returns></returns>
		public static string AddZeromonth(int month)
		{
			if (month.ToString().Length < 2)
				return "0" + month.ToString();
			else
				return month.ToString();
		}

		public static DataTable getAreaSHIlISTByArea(string orderby)
		{
			string sql = "SELECT * FROM TB_JG_AreaSHI_PSL ORDER BY " + orderby;

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}

		public static DataTable getAreaXianList(string areaid, string orderby, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			{
				if (areaid.Substring(4, 2) == "00")
				{
					sb.AppendFormat(" AND areaid like '{0}%'", areaid.Substring(0, 4));
				}
				else
				{
					sb.AppendFormat(" AND areaid ='{0}'", areaid);
				}
			}

			string sql = @"select * from TB_JG_AreaXIANQU_PSL WHERE 1=1 " + sb.ToString() + " order by " + orderby;

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}


		/// <summary>
		/// 医院汇总统计
		/// </summary>
		/// <param name="hosptialname"></param>
		/// <param name="source"></param>
		/// <param name="orderby"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSL_HOS(string areaid, string hosptialname, string source, string orderby, int currentpage, int pagesize, out int totalrecord)
		{
			string where = "";

			if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			{
				if (areaid.Length == 4)
				{
					where += " and areaid like '" + areaid.Substring(0, 4) + "%'";
				}
				else
				{
					where += " and areaid ='" + areaid + "'";
				}
			}

			if (!string.IsNullOrEmpty(hosptialname))
			{
				where += "and HospitalName like '%" + hosptialname + "%'";
			}
			if (!string.IsNullOrEmpty(source) && source != "全部")
			{
				where += "and sortsource = '" + source + "'";
			}

			string sqlWhere = "AND areaid LIKE '22%'";
			string sql = @"select * from TB_JG_Hospital_PSL WHERE 1=1 " + where + sqlWhere + " order by " + orderby;

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}


		/// <summary>
		/// 采购类型汇总
		/// </summary>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSL_SortSource(string where, string orderby, int currentpage, int pagesize, out int totalrecord)
		{

			string sql = @"select * from TB_JG_Goods_Sortsource_PSL WHERE 1=1 " + where + " order by " + orderby;

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 曹鹏
		/// 2011年5月30日17:26:17
		/// 采购类型汇总(根据地区分组)
		/// </summary>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSL_SortSourceByArea(string str, string where, string orderby, int currentpage, int pagesize, out int totalrecord)
		{

			string sql = string.Format("select sortsource,{2} as a, isnull(SUM(PurchaseAmountTotal),0) as PurchaseAmountTotal,isnull(SUM(PurchaseAmountTotal1),0) as PurchaseAmountTotal1,isnull(SUM(PurchaseAmountTotal2),0) as PurchaseAmountTotal2,Avg(isnull(DistributeRate,0)) as DistributeRate,Avg(isnull(WareHouseRate,0)) as WareHouseRate,isnull(SUM(WareHouseAmountTotal),0) as WareHouseAmountTotal,isnull(SUM(DistributeAmountTotal),0) as DistributeAmountTotal from TB_JG_Goods_Sortsource_PSL WHERE 1=1 {0} group by {2},sortsource order by {1}", where, orderby, str);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}


		/// <summary>
		/// 药品类别汇总
		/// by张雄  2011.5.30
		/// </summary>
		/// <param name="sortname"> </param>
		/// <param name="orderby"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSL_Sort(int i, string areaid, string sortname, string orderby, int currentpage, int pagesize, out int totalrecord)
		{
			string where = "";
			string sql = "";

			if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			{
				if (areaid.Length == 4)
				{
					where += " and areaid like '" + areaid.Substring(0, 4) + "%'";
				}
				else
				{
					where += " and areaid ='" + areaid + "'";
				}
			}

			if (!string.IsNullOrEmpty(sortname) && sortname != "全部")
			{
				where += "and SortName = '" + sortname + "'";
			}
			string sqlWhere = "AND AreaID LIKE '22%'";
			if (i == 0)
			{
				sql = @"select * from TB_JG_Goods_Sort_PSL WHERE 1=1 " + where + sqlWhere + " order by " + orderby;
			}
			else
			{
				sql = @"
                        select SortName,SUM(isnull(PurchaseAmountTotal,0)) as PurchaseAmountTotal,
                        isnull(SUM(DistributeAmountTotal),0) as DistributeAmountTotal,
                        Case when sum(PurchaseAmountTotal)=0 then 0 when sum(DistributeAmountTotal)=0 then 0 
                        else CONVERT(float, SUM(DistributeAmountTotal)) / SUM(isnull(PurchaseAmountTotal,0)) end as DistributeRate, 
                        isnull(sum(WareHouseAmountTotal),0) as WareHouseAmountTotal ,
                        Case when sum(PurchaseAmountTotal)=0 then 0 when sum(WareHouseAmountTotal)=0 then 0 
                        else CONVERT(float, SUM(WareHouseAmountTotal)) / SUM(isnull(PurchaseAmountTotal,0)) end as WareHouseRate 
                        from TB_JG_Goods_Sort_PSL where 1=1 " + where + " group by SortName order by " + orderby + "";
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 配送企业列表（高值）
		/// </summary> 
		public static DataTable GetCompanyPS_ZongList(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000" && hosproce.AreaID != "'510000'")
			{
				if (hosproce.AreaID.Length < 5)
				{
					sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				}
				else
				{
					sb.AppendFormat(" and areaid in ({0})", hosproce.AreaID);
				}
			}

			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select CompanyName_PS,max(Companyid_PS) Companyid_PS from View_HospitalProcureCatalog where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyID_PS IS NOT NULL AND CompanyName_PS IS NOT NULL " + sb.ToString() + " group by CompanyName_PS order by CompanyName_PS ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 配送企业列表（低值）
		/// </summary> 
		public static DataTable GetCompanyPS_ZongList_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000" && hosproce.AreaID != "'510000'")
			{
				if (hosproce.AreaID.Length < 5)
				{
					sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				}
				else
				{
					sb.AppendFormat(" and areaid in ({0})", hosproce.AreaID);
				}
			}

			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select CompanyName_PS,max(Companyid_PS) Companyid_PS from View_HospitalProcureCatalogDZ where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyID_PS IS NOT NULL AND CompanyName_PS IS NOT NULL " + sb.ToString() + " group by CompanyName_PS order by CompanyName_PS ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		public static DataTable GetCompanyGoodsList(HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append(DAL_Helper.GetWhereCondition<HZ.Supervise.Model.ProcureCatalog>(proce, false));
			//string sql = @"SELECT * FROM dbo.TB_Goods WHERE 1=1" + sb.ToString() + " ORDER BY GoodsID ";

			string sql = string.Format(@"SELECT a.* FROM dbo.TB_Goods a inner join(
                    select  ProcureCatalogID from View_HospitalProcureCatalog where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL  
                    group by ProcureCatalogID
                    ) b
                    on a.GoodsID = b.ProcureCatalogID where 1=1 {0} ORDER BY GoodsID  ", sb.ToString());
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 产品信息列表（高值）
		/// </summary>
		public static DataTable GetCompanyGoodsListByAreaID(string areaid, HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append(DAL_Helper.GetWhereCondition<HZ.Supervise.Model.ProcureCatalog>(proce, false));
			//string sql = @"SELECT * FROM dbo.TB_Goods WHERE 1=1" + sb.ToString() + " ORDER BY GoodsID ";
			string wwhere = "";
			if (!string.IsNullOrEmpty(areaid))
			{
				if (areaid.Length < 5)//例如 成都市市直
				{
					wwhere = string.Format(" and AreaID like '%{0}%' ", areaid);
				}
				else
				{
					wwhere = string.Format(" and AreaID='{0}' ", areaid);
				}

			}

			string sql = string.Format(@"SELECT a.* FROM dbo.TB_Goods a inner join(
                    select  ProcureCatalogID from View_HospitalProcureCatalog where 1=1 {1} and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL  
                    group by ProcureCatalogID
                    ) b
                    on a.GoodsID = b.ProcureCatalogID where 1=1 {0} ORDER BY GoodsID  ", sb.ToString(), wwhere);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 产品信息列表（低值）
		/// </summary>
		public static DataTable GetCompanyGoodsListByAreaID_DZ(string areaid, HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append(DAL_Helper.GetWhereCondition<HZ.Supervise.Model.ProcureCatalog>(proce, false));
			//string sql = @"SELECT * FROM dbo.TB_Goods WHERE 1=1" + sb.ToString() + " ORDER BY GoodsID ";
			string wwhere = "";
			if (!string.IsNullOrEmpty(areaid))
			{
				if (areaid.Length < 5)//例如 成都市市直
				{
					wwhere = string.Format(" and AreaID like '%{0}%' ", areaid);
				}
				else
				{
					wwhere = string.Format(" and AreaID='{0}' ", areaid);
				}

			}

			string sql = string.Format(@"SELECT a.* FROM dbo.TB_GoodsDZ a inner join(
                    select  ProcureCatalogID from View_HospitalProcureCatalogDZ where 1=1 {1} and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL  
                    group by ProcureCatalogID
                    ) b
                    on a.GoodsID = b.ProcureCatalogID where 1=1 {0} ORDER BY GoodsID  ", sb.ToString(), wwhere);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查询医院详细（高值）
		/// </summary> 
		public static DataTable GetCompanyGoodsList_ps(HZ.Supervise.Model.View.HospitalProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append(DAL_Helper.GetWhereCondition<HZ.Supervise.Model.View.HospitalProcureCatalog>(proce, false));
			string sql = @"select GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,Companyname_PS,CompanyID_PS from View_HospitalProcureCatalog where 1=1 and ISUSING=1  " + sb.ToString() + " order by GoodsID ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查询医院详细（低值） 
		/// </summary> 
		public static DataTable GetCompanyGoodsList_ps_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			sb.Append(DAL_Helper.GetWhereCondition<HZ.Supervise.Model.View.HospitalProcureCatalog>(proce, false));
			string sql = @"select GoodsID,ProcureCatalogID,GoodsName, ProductName,RegName,Brand,Outlookc,Unit,Performance,CompanyName_SC,Companyname_PS,CompanyID_PS from View_HospitalProcureCatalogDZ where 1=1 and ISUSING=1  " + sb.ToString() + " order by GoodsID ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看医院列表（高值）
		/// </summary>  
		public static DataTable GetHospitalInfo(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{

			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyID_PS))
			{
				sb.AppendFormat(" and CompanyID_PS = '{0}'", hosproce.CompanyID_PS);
			}
			if (!string.IsNullOrEmpty(hosproce.HospitalName))
			{
				sb.AppendFormat(" and HospitalName like '%{0}%'", hosproce.HospitalName);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				sb.AppendFormat(" and AreaID like '{0}%'", hosproce.AreaID.Substring(0, 4));
				//if (hosproce.AreaID.Length == 4)
				//{
				//    sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				//}
				//else
				//{
				//    sb.AppendFormat(" and areaid ='{0}'", hosproce.AreaID);
				//}
			}
			string sql = @"select HospitalName from View_HospitalProcureCatalog where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 " + sb.ToString() + " order by HospitalName ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看医院列表（低值） 
		/// </summary>
		public static DataTable GetHospitalInfo_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyID_PS))
			{
				sb.AppendFormat(" and CompanyID_PS = '{0}'", hosproce.CompanyID_PS);
			}
			if (!string.IsNullOrEmpty(hosproce.HospitalName))
			{
				sb.AppendFormat(" and HospitalName like '%{0}%'", hosproce.HospitalName);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				sb.AppendFormat(" and AreaID like '{0}%'", hosproce.AreaID.Substring(0, 4));
			}
			string sql = @"select HospitalName from View_HospitalProcureCatalogDZ where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 " + sb.ToString() + " order by HospitalName ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 产品明细信息（高值）
		/// </summary> 
		public static DataTable GetCompanyPsList_JG_Message(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{

			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyID_PS))
			{
				sb.AppendFormat(" and CompanyID_PS = '{0}'", hosproce.CompanyID_PS);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				if (hosproce.AreaID.Length == 4)
				{
					sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				}
				else
				{
					sb.AppendFormat(" and areaid ='{0}'", hosproce.AreaID);
				}
			}
			if (!string.IsNullOrEmpty(hosproce.HospitalName))
			{
				sb.AppendFormat(" and HospitalName like '%{0}%'", hosproce.HospitalName);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select TOP 1 * from View_HospitalProcureCatalog where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL" + sb.ToString() + " order by ProductName ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 产品明细信息（低值）
		/// </summary> 
		public static DataTable GetCompanyPsList_JG_Message_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{

			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyID_PS))
			{
				sb.AppendFormat(" and CompanyID_PS = '{0}'", hosproce.CompanyID_PS);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				if (hosproce.AreaID.Length == 4)
				{
					sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				}
				else
				{
					sb.AppendFormat(" and areaid ='{0}'", hosproce.AreaID);
				}
			}
			if (!string.IsNullOrEmpty(hosproce.HospitalName))
			{
				sb.AppendFormat(" and HospitalName like '%{0}%'", hosproce.HospitalName);
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select TOP 1 * from View_HospitalProcureCatalogDZ where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL" + sb.ToString() + " order by ProductName ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 产品相关配送企业和医院列表（高值）
		/// </summary> 
		public static DataTable GetCompanyPsList_JG(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{

			StringBuilder sb = new StringBuilder();

			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				//if (hosproce.AreaID.Length == 4)
				//{
				//    sb.AppendFormat(" and areaid like '{0}%'", hosproce.AreaID);
				//}
				//else
				//{
				sb.AppendFormat(" and AreaID like '{0}%'", hosproce.AreaID.Substring(0, 4));
				//}
			}

			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select ProcureCatalogID,CompanyName_PS,Companyid_PS from View_HospitalProcureCatalog where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL" + sb.ToString() + " order by CompanyName_PS ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 产品相关配送企业和医院列表（低值）
		/// </summary> 
		public static DataTable GetCompanyPsList_JG_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sb = new StringBuilder();
			if (!string.IsNullOrEmpty(hosproce.ProcureCatalogID))
			{
				sb.AppendFormat(" and ProcureCatalogID = '{0}'", hosproce.ProcureCatalogID);
			}
			if (!string.IsNullOrEmpty(hosproce.AreaID) && hosproce.AreaID != "510000")
			{
				sb.AppendFormat(" and AreaID like '{0}%'", hosproce.AreaID.Substring(0, 4));
			}
			if (!string.IsNullOrEmpty(hosproce.CompanyName_PS))
			{
				sb.AppendFormat(" and CompanyName_PS like '%{0}%'", hosproce.CompanyName_PS);
			}
			string sql = @"select ProcureCatalogID,CompanyName_PS,Companyid_PS from View_HospitalProcureCatalogDZ where 1=1 and ISUSING=1 AND Hospital_IsUsing=1 AND CompanyName_PS IS NOT NULL AND CompanyID_PS IS NOT NULL" + sb.ToString() + " order by CompanyName_PS ";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 配送企业汇总统计
		/// 张洪莲
		/// </summary>
		/// <param name="vs">View_Supervise</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSLOne(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			//            string sql = "";
			//            string where = "";
			//            //string sort = "";

			//            if (!string.IsNullOrEmpty(sortsource) && sortsource != "全部")
			//            {
			//                where += "and sortsource = '" + sortsource + "'";
			//            }
			//            if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			//            {
			//                if (areaid.Length == 4)
			//                {
			//                    where += " and areaid like '" + areaid.Substring(0, 4) + "%'";
			//                }
			//                else
			//                {
			//                    where += " and areaid ='" + areaid + "'";
			//                }
			//            }
			//            if (!string.IsNullOrEmpty(companyname))
			//            {
			//                where += "and CompanyName_ps like '%" + companyname + "%'";
			//            }


			//            string sqlWhere = "AND areaid LIKE '22%'";

			//            //0.地区分开后的数据信息 1.汇总的信息   by张雄
			//            if (i == 0)
			//            {

			//                sql = @"select * from TB_CompanyPSL_PS where 1=1 " + where + sqlWhere + " order by " + orderby;
			//            }
			//            else
			//            {

			//                sql = @"
			//                        select distinct CompanyName_PS,a.*,case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 
			//                        when a.DistributeAmountTotal/a.PurchaseAmountTotal > 1 then 1 else a.DistributeAmountTotal/a.PurchaseAmountTotal end as DistributeRate,
			//                        case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 when a.WareHouseAmountTotal/a.PurchaseAmountTotal>1 then 1 
			//                        else a.WareHouseAmountTotal/a.PurchaseAmountTotal end as WareHouseRate from dbo.TB_CompanyPSL_PS join (
			//                        select Companyid_ps,sortsource,sum(PurchaseAmountTotal) as PurchaseAmountTotal,
			//                        sum(DistributeAmountTotal) as DistributeAmountTotal,sum(WareHouseAmountTotal) as WareHouseAmountTotal from TB_CompanyPSL_PS 
			//                        where 1=1 " + where + " group by Companyid_ps,sortsource) a on a.Companyid_ps=TB_CompanyPSL_PS.Companyid_ps and  a.sortsource = TB_CompanyPSL_PS.sortsource where 1=1 " + where.Replace("sortsource", "a.sortsource") + " order by " + orderby + "";
			//            }
			string where = " AND OrderDetailState > 0 AND IsUsing = 1 ";

			string areaid = vs.AreaID;
			if (string.IsNullOrEmpty(areaid) || areaid == "510000")
			{
				areaid = "22";
			}
			where += string.Format("AND AreaID LIKE '{0}%'", areaid);
			if (!string.IsNullOrEmpty(Convert.ToString(vs.PlanTimeFrom)))
			{
				where += string.Format(" AND CenSubmiitTime >= '{0}'", vs.PlanTimeFrom);
			}
			if (!string.IsNullOrEmpty(Convert.ToString(vs.PlanTimeTo)))
			{
				where += string.Format(" AND CenSubmiitTime <= '{0}'", vs.PlanTimeTo);
			}
			string strwhere = "";
			if (!string.IsNullOrEmpty(vs.CompanyName_PS))
			{
				strwhere += string.Format(" AND CompanyName  LIKE '%{0}%'", vs.CompanyName_PS);
			}

			string strwhere1 = "";
			if (!string.IsNullOrEmpty(vs.CompanyName_PS))
			{
				strwhere1 += string.Format(" AND CompanyName_PS  LIKE '%{0}%'", vs.CompanyName_PS);
			}
			//判断地区范围

			if (!string.IsNullOrEmpty(vs.IsCountry))
			{
				where += string.Format(" AND iscountry IN({0})", vs.IsCountry);

			}
			if (!string.IsNullOrEmpty(vs.Levels))
			{

				where += string.Format(" AND Levels IN ({0})", vs.Levels);

			}
			string orderby = " ORDER BY PurchaseAmountTotal DESC";
			if (!string.IsNullOrEmpty(vs.SortFields))
			{
				orderby = " ORDER BY " + vs.SortFields;

			} string sql = string.Empty;
			//1汇总、0县区
			if (vs.Flag == "1")
			{
				sql = string.Format(@"SELECT  a.CompanyID ,a.CompanyName ,
                                        CASE WHEN ISNULL(B.PurchaseAmountTotal, '') = '' THEN 0
                                             ELSE B.PurchaseAmountTotal
                                        END PurchaseAmountTotal,
                                        CASE WHEN ISNULL(B.DistributeAmountTotal, '') = '' THEN 0
                                             ELSE B.DistributeAmountTotal
                                        END DistributeAmountTotal,
                                        CASE WHEN ISNULL(B.DistributeCountRate, '') = '' THEN 0
                                             ELSE B.DistributeCountRate
                                        END DistributeCountRate,
                                         CASE WHEN ISNULL(B.WarehouseCountTotal, '') = '' THEN 0
                                             ELSE B.WarehouseCountTotal
                                        END WarehouseCountTotal,
                                        CASE WHEN ISNULL(B.WareHouseRate, '') = '' THEN 0
                                             ELSE B.WareHouseRate
                                        END WareHouseRate
                                FROM    dbo.TB_Company A
                                        LEFT JOIN ( SELECT  CompanyID_PS ,
                                                            MAX(CompanyName_PS) CompanyName_PS ,
                                                            CONVERT(FLOAT,SUM(PurchaseAmount)) PurchaseAmountTotal ,
                                                            CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                                                 WHEN SUM(Price) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                         * Price))
                                                            END AS DistributeAmountTotal ,
                                                            CASE WHEN SUM(PurchaseCount) = 0 THEN 0
                                                                 WHEN SUM(DistributeCount) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT, SUM(DistributeCount))
                                                                      / SUM(PurchaseCount)
                                                            END AS DistributeCountRate ,
                                                            SUM(WarehouseCount) AS WarehouseCountTotal , 
                                                            CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                                                 WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT,SUM(WarehouseAmount)/ SUM(PurchaseAmount))
                                                            END AS WareHouseRate
                                                    FROM    View_Supervise
                                                    WHERE   1 = 1
                                                            {0}
                                                    GROUP BY CompanyID_PS
                                                  ) B ON A.CompanyID = B.CompanyID_PS
                                WHERE   A.CompanyType = 2 
                                {1} {2}", where, strwhere, orderby);
			}
			else
			{
				sql = string.Format(@"SELECT CompanyID_PS  CompanyID ,
                            MAX(CompanyName_PS) CompanyName ,
                            MAX(AreaName) AreaName ,
                            CONVERT(FLOAT, SUM(PurchaseAmount)) PurchaseAmountTotal ,
                            CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                 WHEN SUM(Price) = 0 THEN 0
                                 ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                         * Price))
                            END AS DistributeAmountTotal ,
                            CASE WHEN SUM(PurchaseCount) = 0 THEN 0
                                 WHEN SUM(DistributeCount) = 0 THEN 0
                                 ELSE CONVERT(FLOAT, SUM(DistributeCount))
                                      / SUM(PurchaseCount)
                            END AS DistributeCountRate ,
                            SUM(WarehouseCount) AS WarehouseCountTotal , 
                           CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                 WHEN SUM(PurchaseAmount) = 0 THEN 0
                                 ELSE CONVERT(FLOAT,SUM(WarehouseAmount)/ SUM(PurchaseAmount))
                            END AS WareHouseRate
                    FROM    View_Supervise
                    WHERE   1 = 1
                        {0}  {1}
                    GROUP BY CompanyID_PS ,AreaName
                    {2}  ", where, strwhere1, orderby);
			}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 生产企业汇总统计
		/// by张雄  2011.5.26
		/// </summary>
		/// <param name="companyname">企业名称</param>
		/// <param name="source">属性</param>
		/// <param name="orderby">排序字段</param>
		/// <param name="currentpage">当前页</param>
		/// <param name="pagesize">每页的行数</param>
		/// <param name="totalrecord">总记录</param>
		/// <returns>生产企业汇总统计数据集</returns>
		public static DataTable ExecutePagePSL_SC(int i, string areaid, string companyname, string source, string orderby, int currentpage, int pagesize, out int totalrecord)
		{
			string where = "";
			string sql = "";

			if (!string.IsNullOrEmpty(areaid) && areaid != "510000")
			{
				if (areaid.Length == 4)
				{
					where += " and areaid like '" + areaid.Substring(0, 4) + "%'";
				}
				else
				{
					where += " and areaid ='" + areaid + "'";
				}
			}
			if (!string.IsNullOrEmpty(source) && source != "全部")
			{
				where += " and sortsource = '" + source + "'";
			}
			if (!string.IsNullOrEmpty(companyname))
			{
				where += " and CompanyName_SC like '%" + companyname + "%'";
			}

			string sqlWhere = "AND areaid LIKE '22%'";
			if (i == 0)     //0代表县区 1代表汇总
			{
				sql = @"select * from TB_CompanyPSL_SC where 1=1 " + where + sqlWhere + " order by " + orderby;
			}
			else
			{
				sql = @" 
                        select distinct CompanyName_SC,a.*,case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 
                        when a.DistributeAmountTotal/a.PurchaseAmountTotal >1 then 1 
                        else a.DistributeAmountTotal/a.PurchaseAmountTotal end as DistributeRate,

                        case when isnull(a.PurchaseAmountTotal,0.000)=0.000 then 0 
                        when a.WareHouseAmountTotal/a.PurchaseAmountTotal>1 then 1 
                        else a.WareHouseAmountTotal/a.PurchaseAmountTotal end as WareHouseRate from dbo.TB_CompanyPSL_SC join (
                        select CompanyName_SC as CompanyName_SC1 ,sortsource, 
                        sum(PurchaseAmountTotal) as PurchaseAmountTotal,
                        sum(DistributeAmountTotal) as DistributeAmountTotal, 
                        sum(WareHouseAmountTotal) as WareHouseAmountTotal from TB_CompanyPSL_SC where 1=1 " + where + " group by CompanyName_SC,sortsource ) a on a.CompanyName_SC1=TB_CompanyPSL_SC.CompanyName_SC and  a.sortsource=TB_CompanyPSL_SC.sortsource where 1=1 " + where.Replace("sortsource", "a.sortsource") + " order by " + orderby + "";
			}


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 生产企业汇总
		/// 张洪莲
		/// </summary>
		/// <param name="vs"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable ExecutePagePSL_SC(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string where = " AND OrderDetailState > 0 AND IsUsing = 1 ";

			//判断地区范围

			string areaid = vs.AreaID;
			if (string.IsNullOrEmpty(areaid) || areaid == "510000")
			{
				areaid = "22";
			}
			where += string.Format("AND AreaID LIKE '{0}%'", areaid);
			if (!string.IsNullOrEmpty(Convert.ToString(vs.PlanTimeFrom)))
			{
				where += string.Format(" AND CenSubmiitTime >= '{0}'", vs.PlanTimeFrom);
			}
			if (!string.IsNullOrEmpty(Convert.ToString(vs.PlanTimeTo)))
			{
				where += string.Format(" AND CenSubmiitTime <= '{0}'", vs.PlanTimeTo);
			}
			string strwhere = "";
			if (!string.IsNullOrEmpty(vs.CompanyName_PS))
			{
				strwhere += string.Format(" AND CompanyName LIKE '%{0}%'", vs.CompanyName_PS);
			}
			string strwhere1 = "";
			if (!string.IsNullOrEmpty(vs.CompanyName_PS))
			{
				strwhere1 += string.Format(" AND CompanyName_SC  LIKE '%{0}%'", vs.CompanyName_PS);
			}
			if (!string.IsNullOrEmpty(vs.IsCountry))
			{
				where += string.Format(" AND iscountry IN({0})", vs.IsCountry);

			}
			if (!string.IsNullOrEmpty(vs.Levels))
			{

				where += string.Format(" AND Levels IN ({0})", vs.Levels);

			}
			string orderby = " ORDER BY " + vs.Append;

			string sql = string.Empty;
			//1汇总、0县区
			if (vs.Flag == "1")
			{
				sql = string.Format(@"SELECT  a.CompanyID ,a.CompanyName ,
                                        CASE WHEN ISNULL(B.PurchaseAmountTotal, '') = '' THEN 0
                                             ELSE B.PurchaseAmountTotal
                                        END PurchaseAmountTotal,
                                        CASE WHEN ISNULL(B.DistributeAmountTotal, '') = '' THEN 0
                                             ELSE B.DistributeAmountTotal
                                        END DistributeAmountTotal,
                                        CASE WHEN ISNULL(B.DistributeCountRate, '') = '' THEN 0
                                             ELSE B.DistributeCountRate
                                        END DistributeCountRate,
                                        CASE WHEN ISNULL(B.WarehouseCountTotal, '') = '' THEN 0
                                             ELSE B.WarehouseCountTotal
                                        END WarehouseCountTotal,
                                        CASE WHEN ISNULL(B.WareHouseRate, '') = '' THEN 0
                                             ELSE B.WareHouseRate
                                        END WareHouseRate
                                FROM    dbo.TB_Company A

                                        LEFT JOIN ( SELECT  CompanyID_SC ,
                                                            MAX(CompanyName_SC) CompanyName_SC ,
                                                            CONVERT(FLOAT,SUM(PurchaseAmount)) PurchaseAmountTotal , 
                                                            CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                                                 WHEN SUM(Price) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                         * Price))
                                                            END AS DistributeAmountTotal , 
                                                            CASE WHEN SUM(PurchaseCount) = 0 THEN 0
                                                                 WHEN SUM(DistributeCount) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT, SUM(DistributeCount))
                                                                      / SUM(PurchaseCount)
                                                            END AS DistributeCountRate , 
                                                            SUM(WarehouseCount) AS WarehouseCountTotal , 
                                                            CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                                                 WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                 ELSE CONVERT(FLOAT,SUM(WarehouseAmount)/ SUM(PurchaseAmount))
                                                            END AS WareHouseRate  
                                                    FROM    View_Supervise
                                                    WHERE   1 = 1 {0}
                                                    GROUP BY CompanyID_SC
                                                  ) B ON A.CompanyID = B.CompanyID_SC
                                WHERE   A.CompanyType = 0
                                {1} 
                                {2}

                                ", where, strwhere, orderby);
			}
			else
			{
				sql = string.Format(@"SELECT CompanyID_SC  CompanyID ,
                            MAX(CompanyName_SC) CompanyName ,
                            MAX(AreaName) AreaName ,
                            CONVERT(FLOAT, SUM(PurchaseAmount)) PurchaseAmountTotal ,
                            CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                 WHEN SUM(Price) = 0 THEN 0
                                 ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                         * Price))
                            END AS DistributeAmountTotal ,
                            CASE WHEN SUM(PurchaseCount) = 0 THEN 0
                                 WHEN SUM(DistributeCount) = 0 THEN 0
                                 ELSE CONVERT(FLOAT, SUM(DistributeCount))
                                      / SUM(PurchaseCount)
                            END AS DistributeCountRate ,
                            SUM(WarehouseCount) AS WarehouseCountTotal ,
                            CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                 WHEN SUM(PurchaseAmount) = 0 THEN 0
                                 ELSE CONVERT(FLOAT,SUM(WarehouseAmount)/ SUM(PurchaseAmount))
                            END AS WareHouseRate
                    FROM    View_Supervise
                    WHERE   1 = 1
                     {0}  {1}
                    GROUP BY CompanyID_SC ,AreaName
                    {2}", where, strwhere1, orderby);
			}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 曹鹏 2011年5月18日11:09:04
		/// 医院季报
		/// </summary>
		/// <param name="where"></param>
		/// <returns></returns>
		public static DataTable StatisticsHospitalBySeason(string where)
		{

			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime", where);

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

		}

		/// <summary>
		/// 医院季报 饼状
		/// by张雄  2011.5.19
		/// </summary>
		/// <param name="where">where条件参数</param>
		/// <returns></returns>
		public static DataTable StatisticsHospitalBySeasonPie(string where)
		{
			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) > 1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) > 1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,case SortSource when '0' then '日常采购' when '1' then '临时采购' when '2' then '应急采购' else '无'	end as SortSource from CACHE_STAT_Goods WHERE 1=1  {0} group by SortSource", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月18日11:09:24
		/// 医院月报
		/// </summary>
		/// <param name="where"></param>
		/// <returns></returns>
		public static DataTable StatisticsHospitalByMonth(string where)
		{

			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,SortSource,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource,daytime", where);

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月18日11:09:34 
		/// 医院年报
		/// </summary>
		/// <param name="where"></param>
		/// <returns></returns>
		public static DataTable StatisticsHospitalByYear(string where)
		{

			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount) as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime", where);

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

		}
		/// <summary>
		/// 医院年报    报表
		/// by张雄  2011.5.24
		/// </summary>
		/// <param name="where">条件</param>
		/// <returns>医院年报数据</returns>
		public static DataTable StatisticsHospitalByYearNew(string where)
		{

			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount) as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime", where);

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

		}

		/// <summary>
		/// 曹鹏
		/// 2011年5月18日11:09:34 
		/// 医院年报
		/// </summary>
		/// <param name="where"></param>
		/// <returns></returns>
		public static DataTable StatisticsHospitalByYearPie(string where)
		{

			string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount) as WarehouseAmountTotal,case SortSource when '0' then '日常采购' when '1' then '临时采购' when '2' then '应急采购' else '无'  end as SortSource  from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource", where);

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月20日9:23:29
		/// 医院某采购类型所在比例
		/// </summary>
		/// <param name="hosName"></param>
		/// <param name="sortSource"></param>
		/// <param name="rate"></param>
		/// <param name="compare"></param>
		/// <param name="areaid"></param>
		/// <param name="pageSize"></param>
		/// <param name="currentPageIndex"></param>
		/// <param name="total"></param>
		/// <returns></returns>
		public static DataTable ExecuteRateOfHospitalSource(string sbWhere, string scWhere, string whereareaid, string sort, string strSortSource, int pageSize, int currentPageIndex, out int total)
		{
			//by张雄 sql修改
			string sql1 = string.Format(@"
                                        select  B.PurchaseAmountTotal1,A.AreaID,A.DayTime,A.HospitalName,A.SortSource,A.PurchaseAmountTotal,
                                        A.DistributeAmountTotal,A.WarehouseAmountTotal,A.DistributeRate,A.WarehouseRate,B.sortsourcerate from (
                                        select HospitalName, AreaID, SortSorce as SortSource,max(CAST( DayTime as varchar(50) )) as DayTime,
                                        SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,
                                        SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 
                                        when CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                        else CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount))) end as DistributeRate,
                                        case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 
                                        when CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                        else CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount))) end as WarehouseRate  
                                        from CACHE_STAT  where 1=1 {0} {1}  group by HospitalName,SortSorce, AreaID
                                        ) A inner join (
                                        select A.PurchaseAmountTotal as PurchaseAmountTotal1,case when B.PurchaseAmountTotal1 = 0 then 0 
                                        when A.PurchaseAmountTotal=0 then 0 else CONVERT(decimal(18,3),B.PurchaseAmountTotal1/A.PurchaseAmountTotal) end as sortsourcerate ,
                                        B.SortSource as sortsource,A.HospitalName  from (select SUM(PurchaseAmount) as PurchaseAmountTotal,HospitalName 
                                        from CACHE_STAT where 1=1 {0} {1} group by HospitalName
                                        )  A inner join (
                                        select sum(PurchaseAmount) as PurchaseAmountTotal1,HospitalName,sortsorce as SortSource  from CACHE_STAT 
                                        where 1=1 {0} {1} {4}group by HospitalName,SortSorce

                                        ) B on A.HospitalName = B.HospitalName 
                                        ) B on A.HospitalName= B.HospitalName and A.SortSource=B.sortsource where 1=1 {3} order by {2}", sbWhere, whereareaid, sort, scWhere, strSortSource);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql1, pageSize);

			des.CurrentPage = currentPageIndex;

			total = des.TotalCount;

			return des.GetSpecailPage();

		}

		/// <summary>
		/// 采购数量
		/// by：张雄
		/// </summary>
		/// <param name="sb">日期条件</param>
		/// <param name="areaid">地区条件</param>
		/// <returns></returns>
		public static DataTable ExecuteRateOfHosSourceCount(string sb, string areaid, string flag, int pageSize, int currentPageIndex, out int t)
		{

			string sql = "";        //flag = 0;代表按照医院统计 。 flag = 1 代表按照县区统计 flag = 2代表按照市统计

			if (flag == "0")
			{
				//医院统计：采购次数统计卫生院发送订单的次数，一天内发送的多条订单算做一个。
				sql = string.Format(@"
                                         select * from 
                                         (select count(1) CGCount,a.HospitalID,MAX(HospitalName) HospitalName from ( 
                                         SELECT distinct CONVERT(VARCHAR(30),PlanTime,111) as CGCount, HospitalID 
                                         from View_PurchaseOrder where 1=1 and OrderStatus=1  {0}{1}
                                         )a join tb_Hospital on tb_Hospital.HospitalID=a.HospitalID group by a.HospitalID
                                         ) t order by HospitalID 

                                        ", sb, areaid);
			}
			else if (flag == "1")
			{
				//县区统计：县区的采购次数为该区域内所有卫生院的平均采购次数（采购单总数/医院个数）。
				sql = string.Format(@"
                                     select * from (   
                                     select b.areaid,sum(CGCount)/count(1) as CGCount,max(areaname) as areaname from (
                                     select count(1) CGCount,HospitalID,max(areaid) as areaid from ( 
                                     SELECT distinct CONVERT(VARCHAR(30),PlanTime,111) as CGCount, HospitalID,areaid 
                                     from View_PurchaseOrder where 1=1 and OrderStatus=1 {0}{1}
                                     )a  group by HospitalID
                                     ) b join tb_area on tb_area.areaid=b.areaid group by b.areaid 
                                     ) t order by CGCount desc
                                    ", sb, areaid);
			}
			else
			{
				//统计市
				sql = string.Format(@"
                                     select * from (   
                                     select b.areaid,(sum(CGCount)+count(1)/2)/count(1) as CGCount,max(areaname) as areaname from (
                                     select count(1) CGCount,HospitalID,max(areaid) as areaid from ( 
                                     SELECT distinct CONVERT(VARCHAR(30),PlanTime,111) as CGCount, HospitalID,substring(areaid,0,5)+'00' as areaid 
                                     from View_PurchaseOrder where 1=1 and OrderStatus=1 {0}{1}
                                     )a  group by HospitalID
                                     ) b join tb_area on tb_area.areaid=b.areaid group by b.areaid 
                                     ) t order by CGCount desc
                                    ", sb, areaid);
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);

			des.CurrentPage = currentPageIndex;

			t = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 曹鹏
		/// 2011年5月20日14:41:07
		/// 地区某采购类型所在比例（县区）
		/// </summary>
		/// <param name="hosName"></param>
		/// <param name="sortSource"></param>
		/// <param name="rate"></param>
		/// <param name="compare"></param>
		/// <param name="areaid"></param>
		/// <param name="pageSize"></param>
		/// <param name="currentPageIndex"></param>
		/// <param name="total"></param>
		/// <returns></returns>
		public static DataTable ExecuteRateOfHAreaSource(string sbWhere, string scWhere, string whereareaid, string sort, string strSortSource, int pageSize, int currentPageIndex, out int total)
		{
			//sql修改 by张雄
			string sql = string.Format(@"
                                            select B.PurchaseAmountTotal1,A.DayTime,A.AreaName,A.DistributeAmountTotal,A.PurchaseAmountTotal,
                                            A.WarehouseAmountTotal,A.DistributeRate,A.WarehouseRate,A.SortSource,B.sortsourcerate from (

                                            select AreaID,areaname, SortSorce as SortSource,max(CAST( DayTime as varchar(50) )) as DayTime,
                                            SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,
                                            SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 
                                            when SUM(PurchaseAmount)=0 then 0 
                                            when CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                            else CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount))) end as DistributeRate,
                                            case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 
                                            when CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                            else CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount))) end as WarehouseRate 
                                            from CACHE_STAT where 1=1 {0} {1}  group by areaname,SortSorce, AreaID 

                                            ) A inner join (
                                            select A.PurchaseAmountTotal as PurchaseAmountTotal1,case when B.PurchaseAmountTotal1 = 0 then 0 
                                            when A.PurchaseAmountTotal=0 then 0 else CONVERT(decimal(18,3),B.PurchaseAmountTotal1/A.PurchaseAmountTotal) end as sortsourcerate ,
                                            B.SortSource as sortsource,A.AreaName  from (

                                            select SUM(PurchaseAmount) as PurchaseAmountTotal,AreaName from CACHE_STAT where 1=1 {0} {1} group by AreaName

                                            )  A inner join (

                                            select sum(PurchaseAmount) as PurchaseAmountTotal1,AreaName,sortsorce as SortSource  from CACHE_STAT 
                                            where 1=1 {0} {1} {4} group by AreaName,SortSorce)  B on A.AreaName = B.AreaName

                                            ) B on A.AreaName=B.AreaName and A.SortSource=B.sortsource where 1=1 {3}  order by {2}", sbWhere, whereareaid, sort, scWhere, strSortSource);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);

			des.CurrentPage = currentPageIndex;

			total = des.TotalCount;

			return des.GetSpecailPage();

		}

		/// <summary>
		/// 曹鹏
		/// 2011年5月20日14:41:07
		/// 地区某采购类型所在比例（城市）
		/// </summary>
		/// <param name="hosName"></param>
		/// <param name="sortSource"></param>
		/// <param name="rate"></param>
		/// <param name="compare"></param>
		/// <param name="areaid"></param>
		/// <param name="pageSize"></param>
		/// <param name="currentPageIndex"></param>
		/// <param name="total"></param>
		/// <returns></returns>
		public static DataTable ExecuteRateOfHAreaSource1(string timeFrom, string timeTo, string areaGroupBy, string sbWhere, string scWhere, string sort, string strSortSource, int pageSize, int currentPageIndex, out int total)
		{
			string str = " AND A.AreaID LIKE '22%' ";
			//sql修改 by张雄
			string sql = string.Format(@"
                                        select C.AreaName,B.PurchaseAmountTotal1,A.DayTime,A.AreaID,A.DistributeAmountTotal,A.PurchaseAmountTotal,
                                        A.WarehouseAmountTotal,A.DistributeRate,A.WarehouseRate,A.SortSource,B.sortsourcerate from (
                                        select {0} as AreaID, SortSorce as SortSource,max(CAST( DayTime as varchar(50) )) as DayTime,
                                        SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,
                                        SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 
                                        when SUM(PurchaseAmount)=0 then 0 
                                        when CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                        else CONVERT(decimal(18,3),CONVERT(float,sum(DistributeAmount))/CONVERT(float,sum(PurchaseAmount))) end as DistributeRate,
                                        case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 
                                        when CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount)))>1 then 1 
                                        else CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,sum(PurchaseAmount))) end as WarehouseRate  
                                        from CACHE_STAT where 1=1  {1}  group by {0} ,SortSorce
                                        ) A inner join (
                                        select A.PurchaseAmountTotal as PurchaseAmountTotal1,case when B.PurchaseAmountTotal1 = 0 then 0 
                                        when A.PurchaseAmountTotal=0 then 0 else CONVERT(decimal(18,3),B.PurchaseAmountTotal1/A.PurchaseAmountTotal) end as sortsourcerate ,
                                        B.SortSource as sortsource,A.AreaID  from (
                                        select SUM(PurchaseAmount) as PurchaseAmountTotal,{0} as AreaID from CACHE_STAT where 1=1  {1} group by {0}

                                        )  A inner join (

                                        select sum(PurchaseAmount) as PurchaseAmountTotal1,sortsorce as SortSource,{0} as AreaID  from CACHE_STAT 
                                        where 1=1  {1} {4} group by {0},SortSorce

                                        )  B on A.AreaID = B.AreaID) B on A.AreaID=B.AreaID and A.SortSource=B.sortsource inner join TB_Area C 
                                        on A.AreaID+'00' = C.AreaID where 1=1 {3} {5}  order by {2}", areaGroupBy, sbWhere, sort, scWhere, strSortSource, str);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);

			des.CurrentPage = currentPageIndex;

			total = des.TotalCount;

			return des.GetSpecailPage();

		}

		/// <summary>
		/// 订单明细查询（高值）
		/// </summary>
		/// <param name="where"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GoodsOrderDetail(string where, int i, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			if (i == 0)
			{
				sql = string.Format(@" SELECT  GoodsID,
                                GoodsName,
                                ProductName,
                                Outlookc,
                                Factor,
                                Unit,
                                AreaID,
                                AreaName,
                                companyName_TB,
                                CompanyName_PS,
                                HospitalName,
                                OrderName,
                                Price,
                                PurchaseCount,
                                DistributeCount,
                                WarehouseCount,
                                PurchaseAmount,
                                ( DistributeCount * Price ) DistributeAmount,
                                WarehouseAmount,
                                SubmiTime,
                                OrderDetailState
                        FROM    View_Supervise
                        WHERE   OrderDetailState > 0
                                AND PurchaseAmount > 0
                                {0}
                        ORDER BY OrderDetailState", where);
			}
			if (i == 1)
			{
				sql = string.Format(@"SELECT ISNULL(SUM(PurchaseCount*Price),0)PurchaseOrderAmount,ISNULL(SUM(DistributeCount*Price),0) DistributeOrderAmount,ISNULL(SUM(WarehouseCount*Price),0) WarehouseOrederAmount 
                                        FROM dbo.View_Supervise WHERE 1=1 AND OrderDetailState > 0 AND PurchaseCount > 0 {0} ORDER BY PurchaseOrderAmount", where);
			}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 订单明细查询（低值）
		/// </summary>
		/// <param name="where"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GoodsOrderDetail_DZ(string where, int i, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			if (i == 0)
			{
				sql = string.Format(@" SELECT  GoodsID,
                                GoodsName,
                                ProductName,
                                Outlookc,
                                Factor,
                                Unit,
                                AreaID,
                                AreaName,
                                companyName_TB,
                                CompanyName_PS,
                                HospitalName,
                                OrderName,
                                Price,
                                PurchaseCount,
                                DistributeCount,
                                WarehouseCount,
                                PurchaseAmount,
                                ( DistributeCount * Price ) DistributeAmount,
                                WarehouseAmount,
                                SubmiTime,
                                OrderDetailState
                        FROM    View_SuperviseDZ
                        WHERE   OrderDetailState > 0
                                AND PurchaseAmount > 0
                                {0}
                        ORDER BY OrderDetailState", where);
			}
			if (i == 1)
			{
				sql = string.Format(@"SELECT ISNULL(SUM(PurchaseCount*Price),0)PurchaseOrderAmount,ISNULL(SUM(DistributeCount*Price),0) DistributeOrderAmount,ISNULL(SUM(WarehouseCount*Price),0) WarehouseOrederAmount 
                                        FROM dbo.View_SuperviseDZ WHERE 1=1 AND OrderDetailState > 0 AND PurchaseCount > 0 {0} ORDER BY PurchaseOrderAmount", where);
			}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 生产企业分类汇总
		/// </summary>
		/// <param name="where"></param>
		/// <param name="i"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable CompanySC_InfoByType(SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			Hashtable hs = getSql(vs);

			sql = string.Format(@" SELECT CompanyName_SC ,
                                                                CompanyID_SC ,
                                                                ProductName ,
                                                               MAX(MedicineModelName) MedicineModelName,
                                                               MAX(Outlookc) Outlookc,
                                                               MAX(Pack) Pack,
                                                                CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseCount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                             * Price))
                                                                          / SUM(ISNULL(PurchaseAmount, 0))
                                                                END AS DistributeRate ,
                                                                SUM(PurchaseAmount) AS PurchaseAmountTotal ,
                                                                CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(WarehouseAmount))
                                                                          / SUM(ISNULL(PurchaseAmount, 0))
                                                                END AS WareHouseRate ,
                                                                SUM(WarehouseAmount) AS WareHouseAmountTotal ,
                                                                SUM(DistributeCount * Price) AS DistributeAmountTotal ,
                                                                COUNT(DISTINCT ProcureCatalogID) AS DrugCount
                                                         FROM   View_Supervise
                                                        
                                                      
                                    WHERE   1 = 1  AND OrderDetailState > 0 AND IsUsing = 1 {0}
                                     GROUP BY CompanyName_SC ,
                                                                ProductName ,
                                                                CompanyID_SC
                                    HAVING 1=1 {1}
                                    ORDER BY {2}", hs["where"].ToString(), hs["where1"].ToString(), vs.Append);


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 配送企业分类汇总
		/// </summary>
		/// <param name="where"></param>
		/// <param name="i"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable CompanyPS_InfoByType(SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			Hashtable hs = getSql(vs);

			sql = string.Format(@" SELECT CompanyName_PS ,
                                                                CompanyID_PS ,
                                                                ProductName ,
                                                               MAX(MedicineModelName) MedicineModelName,
                                                               MAX(Outlookc) Outlookc,
                                                               MAX(Pack) Pack,
                                                                CASE WHEN SUM(DistributeCount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseCount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(DistributeCount
                                                                                             * Price))
                                                                          / SUM(ISNULL(PurchaseAmount, 0))
                                                                END AS DistributeRate ,
                                                                SUM(PurchaseAmount) AS PurchaseAmountTotal ,
                                                                CASE WHEN SUM(WarehouseAmount) = 0 THEN 0
                                                                     WHEN SUM(PurchaseAmount) = 0 THEN 0
                                                                     ELSE CONVERT(FLOAT, SUM(WarehouseAmount))
                                                                          / SUM(ISNULL(PurchaseAmount, 0))
                                                                END AS WareHouseRate ,
                                                                SUM(WarehouseAmount) AS WareHouseAmountTotal ,
                                                                SUM(DistributeCount * Price) AS DistributeAmountTotal ,
                                                                COUNT(DISTINCT ProcureCatalogID) AS DrugCount
                                                         FROM   View_Supervise
                                                        
                                                      
                                    WHERE   1 = 1  AND OrderDetailState > 0 AND IsUsing = 1 {0}
                                     GROUP BY CompanyName_PS ,
                                                                ProductName ,
                                                                CompanyID_PS
                                    HAVING 1=1 {1}
                                    ORDER BY {2}", hs["where"].ToString(), hs["where1"].ToString(), vs.Append);


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		public static Hashtable getSql(SuperviseQuery vs)
		{
			Hashtable hs = new Hashtable();
			StringBuilder sb = new StringBuilder();
			StringBuilder sb1 = new StringBuilder();
			sb.Append(" AND OrderDetailState > 0 AND IsUsing = 1 ");

			//判断地区范围

			string areaid = vs.AreaID;
			if (string.IsNullOrEmpty(areaid) || areaid == "510000")
			{
				areaid = "13";
			}
			sb.AppendFormat("AND AreaID LIKE '{0}%'", areaid);
			if (vs.Flag == "1")
			{
				if (!string.IsNullOrEmpty(vs.ProductName))
				{
					sb.AppendFormat(" AND ProductName = '{0}'", vs.ProductName);

				}
				if (!string.IsNullOrEmpty(vs.PinyinCode))
				{
					sb.AppendFormat("AND PinyinCode= '{0}'", vs.PinyinCode);
				}
			}

			else
			{
				if (!string.IsNullOrEmpty(vs.ProductName))
				{
					sb.AppendFormat(" AND ProductName like '%{0}%'", vs.ProductName);

				}
				if (!string.IsNullOrEmpty(vs.PinyinCode))
				{
					sb.AppendFormat("AND PinyinCode like '%{0}%'", vs.PinyinCode);
				}
			}


			if (vs.CenSubmiitTimeFrom != null)
			{
				sb.AppendFormat(" AND CenSubmiitTime >= '{0}'", vs.CenSubmiitTimeFrom);
			}

			if (vs.CenSubmiitTimeTo != null)
			{
				sb.AppendFormat(" AND CenSubmiitTime <= '{0}'", vs.CenSubmiitTimeTo);
			}

			if (!string.IsNullOrEmpty(vs.MedicineModelName))
			{
				sb.AppendFormat(" AND MedicineModelName LIKE '%{0}%'", vs.MedicineModelName);
			}
			if (!string.IsNullOrEmpty(vs.Pack))
			{
				sb.AppendFormat(" AND Pack LIKE '%{0}%'", vs.Pack);
			}
			if (!string.IsNullOrEmpty(vs.Unit))
			{
				sb.AppendFormat(" AND Unit LIKE '%{0}%'", vs.Unit);
			}
			if (!string.IsNullOrEmpty(vs.HospitalName))
			{
				sb.AppendFormat(" AND HospitalName LIKE '%{0}%'", vs.HospitalName);
			}


			if (!string.IsNullOrEmpty(vs.Levels))
			{
				sb.AppendFormat(" AND Levels IN ({0})", vs.Levels);
			}



			if (!string.IsNullOrEmpty(vs.IsCountry))
			{
				sb.AppendFormat(" AND isCountry IN ({0})", vs.IsCountry);
			}

			if (vs.WarehouseAmountFrom != null)
			{
				sb1.AppendFormat(" AND SUM(WarehouseAmount) >={0}", vs.WarehouseAmountFrom);

			}
			if (vs.WarehouseAmountTo != null)
			{

				sb1.AppendFormat(" AND  SUM(WarehouseAmount) <={0}", vs.WarehouseAmountTo);
			}

			hs.Add("where", sb);
			hs.Add("where1", sb1);
			return hs;
		}

		/// <summary>
		/// 药品汇总统计（高值）
		/// </summary>
		/// <param name="i">哪个数据源</param>
		/// <param name="strwhere">where条件</param> 
		public static DataTable GetGoodsStatistics(int i, string strwhere, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = "";
			if (i == 1)
			{
				SQL = string.Format(@"
                                                        SELECT  GoodsID,
                                                                    GoodsName,
                                                                    Outlookc,
                                                                    Unit,
                                                                    BidPrice,
                                                                    Price,
                                                                    CompanyID_TB,
                                                                    CompanyName_TB,
                                                                    ISNULL(PurchaseAmount, 0) PurchaseAmount,
                                                                    ISNULL(DistributeAmount, 0) DistributeAmount,
                                                                    ISNULL(WarehouseAmount, 0) WarehouseAmount,
                                                                    ISNULL(ReturnAmount, 0) ReturnAmount
                                                            FROM    ( SELECT    C.GoodsID,
                                                                                C.GoodsName,
                                                                                C.Outlookc,
                                                                                C.Unit,
                                                                                C.BidPrice,
                                                                                Price,
                                                                                C.CompanyID_TB,
                                                                                C.CompanyName_TB,
                                                                                SUM(PurchaseCount * Price) PurchaseAmount,
                                                                                SUM(DistributeCount * Price) DistributeAmount,
                                                                                SUM(WarehouseCount * Price) WarehouseAmount
                                                                      FROM      dbo.TB_PurchaseOrder A
                                                                                INNER JOIN dbo.TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                                                                INNER JOIN dbo.TB_Goods C ON B.ProcureCatalogID = C.GoodsID
                                                                      WHERE     1 = 1 AND OrderDetailState > 0 AND PurchaseCount > 0
                                                                      GROUP BY  C.GoodsID,
                                                                                C.GoodsName,
                                                                                C.Outlookc,
                                                                                C.Unit,
                                                                                C.BidPrice,
                                                                                Price,
                                                                                C.CompanyID_TB,
                                                                                C.CompanyName_TB
                                                                    ) T
                                                                    LEFT JOIN ( SELECT  ProcureCatalogID,
                                                                                        ABS(SUM(Price * PurchaseCount)) ReturnAmount
                                                                                FROM    dbo.TB_PurchaseOrder A
                                                                                        INNER JOIN dbo.TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                                                                WHERE   PurchaseCount < 0
                                                                                        and OrderDetailState > 0
                                                                                GROUP BY ProcureCatalogID
                                                                              ) V ON T.GoodsID = V.ProcureCatalogID
                                                                              WHERE 1=1 {0}
                                                    ", strwhere);
			}
			else if (i == 2)
			{
				SQL = string.Format(@"
                                                    SELECT  
                                                    ISNULL(SUM(PurchaseAmount), 0) PurchaseAmount,
                                                    ISNULL(SUM(DistributeAmount), 0) DistributeAmount,
                                                    ISNULL(SUM(WarehouseAmount), 0) WarehouseAmount,
                                                    ISNULL(SUM(ReturnAmount), 0) ReturnAmount
                                            FROM    ( SELECT    C.GoodsID,
                                                                SUM(PurchaseCount * Price) PurchaseAmount,
                                                                SUM(DistributeCount * Price) DistributeAmount,
                                                                SUM(WarehouseCount * Price) WarehouseAmount
                                                      FROM      dbo.TB_PurchaseOrder A
                                                                INNER JOIN dbo.TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                                                INNER JOIN dbo.TB_Goods C ON B.ProcureCatalogID = C.GoodsID
                                                      WHERE     1 = 1  AND OrderDetailState > 0 AND PurchaseCount > 0
                                                      GROUP BY  C.GoodsID
                                                    ) T
                                                    LEFT JOIN ( SELECT  ProcureCatalogID,
                                                                        ABS(SUM(Price * PurchaseCount)) ReturnAmount
                                                                FROM    dbo.TB_PurchaseOrder A
                                                                        INNER JOIN dbo.TB_PurchaseOrderDetail B ON A.OrderID = B.OrderID
                                                                WHERE   PurchaseCount < 0
                                                                        and OrderDetailState > 0
                                                                GROUP BY ProcureCatalogID
                                                              ) V ON T.GoodsID = V.ProcureCatalogID
                                                              WHERE 1=1                
                                                ");
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 药品汇总统计（低值）
		/// </summary>
		/// <param name="i">哪个数据源</param>
		/// <param name="strwhere">where条件</param> 
		public static DataTable GetGoodsStatistics_DZ(int i, string strwhere, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = "";
			if (i == 1)
			{
				SQL = string.Format(@"
                                                        SELECT  GoodsID,
                                                                    GoodsName,
                                                                    Outlookc,
                                                                    Unit,
                                                                    BidPrice,
                                                                    Price,
                                                                    CompanyID_TB,
                                                                    CompanyName_TB,
                                                                    ISNULL(PurchaseAmount, 0) PurchaseAmount,
                                                                    ISNULL(DistributeAmount, 0) DistributeAmount,
                                                                    ISNULL(WarehouseAmount, 0) WarehouseAmount,
                                                                    ISNULL(ReturnAmount, 0) ReturnAmount
                                                            FROM    ( SELECT    C.GoodsID,
                                                                                C.GoodsName,
                                                                                C.Outlookc,
                                                                                C.Unit,
                                                                                C.BidPrice,
                                                                                Price,
                                                                                C.CompanyID_TB,
                                                                                C.CompanyName_TB,
                                                                                SUM(PurchaseCount * Price) PurchaseAmount,
                                                                                SUM(DistributeCount * Price) DistributeAmount,
                                                                                SUM(WarehouseCount * Price) WarehouseAmount
                                                                      FROM      dbo.TB_PurchaseOrderDZ A
                                                                                INNER JOIN dbo.TB_PurchaseOrderDetailDZ B ON A.OrderID = B.OrderID
                                                                                INNER JOIN dbo.TB_GoodsDZ C ON B.ProcureCatalogID = C.GoodsID
                                                                      WHERE     1 = 1 AND OrderDetailState > 0 AND PurchaseCount > 0
                                                                      GROUP BY  C.GoodsID,
                                                                                C.GoodsName,
                                                                                C.Outlookc,
                                                                                C.Unit,
                                                                                C.BidPrice,
                                                                                Price,
                                                                                C.CompanyID_TB,
                                                                                C.CompanyName_TB
                                                                    ) T
                                                                    LEFT JOIN ( SELECT  ProcureCatalogID,
                                                                                        ABS(SUM(Price * PurchaseCount)) ReturnAmount
                                                                                FROM    dbo.TB_PurchaseOrderDZ A
                                                                                        INNER JOIN dbo.TB_PurchaseOrderDetailDZ B ON A.OrderID = B.OrderID
                                                                                WHERE   PurchaseCount < 0
                                                                                        and OrderDetailState > 0
                                                                                GROUP BY ProcureCatalogID
                                                                              ) V ON T.GoodsID = V.ProcureCatalogID
                                                                              WHERE 1=1 {0}
                                                    ", strwhere);
			}
			else if (i == 2)
			{
				SQL = string.Format(@"
                                                    SELECT  
                                                    ISNULL(SUM(PurchaseAmount), 0) PurchaseAmount,
                                                    ISNULL(SUM(DistributeAmount), 0) DistributeAmount,
                                                    ISNULL(SUM(WarehouseAmount), 0) WarehouseAmount,
                                                    ISNULL(SUM(ReturnAmount), 0) ReturnAmount
                                            FROM    ( SELECT    C.GoodsID,
                                                                SUM(PurchaseCount * Price) PurchaseAmount,
                                                                SUM(DistributeCount * Price) DistributeAmount,
                                                                SUM(WarehouseCount * Price) WarehouseAmount
                                                      FROM      dbo.TB_PurchaseOrderDZ A
                                                                INNER JOIN dbo.TB_PurchaseOrderDetailDZ B ON A.OrderID = B.OrderID
                                                                INNER JOIN dbo.TB_GoodsDZ C ON B.ProcureCatalogID = C.GoodsID
                                                      WHERE     1 = 1  AND OrderDetailState > 0 AND PurchaseCount > 0
                                                      GROUP BY  C.GoodsID
                                                    ) T
                                                    LEFT JOIN ( SELECT  ProcureCatalogID,
                                                                        ABS(SUM(Price * PurchaseCount)) ReturnAmount
                                                                FROM    dbo.TB_PurchaseOrderDZ A
                                                                        INNER JOIN dbo.TB_PurchaseOrderDetailDZ B ON A.OrderID = B.OrderID
                                                                WHERE   PurchaseCount < 0
                                                                        and OrderDetailState > 0
                                                                GROUP BY ProcureCatalogID
                                                              ) V ON T.GoodsID = V.ProcureCatalogID
                                                              WHERE 1=1                
                                                ");
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 医院统计
		/// </summary>
		/// <param name="i">1代表医院统计，2代表医院下面药品统计</param>
		/// <param name="timeto">结束时间</param>
		/// <param name="timeform">起始时间</param>
		/// <param name="strwhere">where条件</param>
		/// <param name="orderby">排序方式</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalStatistics(int i, string IsCountry, string timeto, string timeform, string strwhere, string orderby, string areaid, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";

			#region 医院统计
			if (i == 1)
			{
				//医院角度
				sql = string.Format(@" 
                                                  
                                                  SELECT Hos.*,
                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,  
                                                        ISNULL(Pur.ComResonseAmount, 0) ComResonseAmount,  
                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
                                                         ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
                                                 FROM   ( SELECT    HospitalID,
                                                                    HospitalName
                                                          FROM      dbo.TB_Role_User A
                                                                    INNER JOIN dbo.TB_Hospital B ON A.UserID = B.HospitalID
                                                          WHERE     A.RoleID = '01'
                                                                    AND SNKEY <> '1'
                                                                    AND AreaID LIKE '{2}%' {3}
                                                        ) Hos
                                                        LEFT JOIN ( SELECT  HospitalID,
                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
                                                                            SUM(CASE WHEN OrderDetailState = 2
                                                                                     THEN Price * PurchaseCount
                                                                                END) ComResonseAmount,
                                                                            SUM(Price*DistributeCount) DistributeAmount,
                                                                            SUM(Price*WarehouseCount) WarehouseAmount
                                                                    FROM    View_PurchaseOrderDetail
                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and SubmiTime between '{0}' and '{1}'
                                                                      and AreaID like '{2}%' {3}
                                                                    GROUP BY HospitalID
                                                                  ) Pur ON Hos.HospitalID = Pur.HospitalID
                                                        LEFT JOIN ( sELECT  HospitalID,
                                                                            abs(SUM(Price * PurchaseCount)) ReturnAmount
                                                                    FROM    View_PurchaseOrderDetail
                                                                    WHERE   PurchaseCount<0 and OrderDetailState > 0 and SubmiTime between '{0}' and '{1}'
                                                                    and AreaID like '{2}%' {3}
                                                                    GROUP BY HospitalID
                                                                  ) ReturnMer ON Hos.HospitalID = ReturnMer.HospitalID
                                                 ORDER BY {4} 
             
            ", Convert.ToDateTime(timeform).ToString(), Convert.ToDateTime(timeto).ToString(), areaid, strwhere, orderby);
			}
			//配送企业汇总
			else if (i == 2)
			{
				sql = string.Format(@"SELECT Hos.*,
                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,   
                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
                                                         ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
                                                 FROM   ( SELECT    CompanyID,
                                                            CompanyName
                                                  FROM      TB_Company
                                                  WHERE     CompanyType = 2 {3}
                                                        ) Hos
                                                        LEFT JOIN ( SELECT  CompanyID_PS,
                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
                                                                            SUM(Price*DistributeCount) DistributeAmount,
                                                                            SUM(Price*WarehouseCount) WarehouseAmount
                                                                    FROM    View_PurchaseOrderDetail
                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and SubmiTime between '{0}' and '{1}'
                                                                      and AreaID like '{2}%' {3}
                                                                    GROUP BY CompanyID_PS
                                                                  ) Pur ON Hos.CompanyID = Pur.CompanyID_PS
                                                        LEFT JOIN ( sELECT  CompanyID_PS,
                                                                            abs(SUM(Price * PurchaseCount)) ReturnAmount
                                                                    FROM    View_PurchaseOrderDetail
                                                                    WHERE   PurchaseCount<0 and OrderDetailState > 0 and SubmiTime between '{0}' and '{1}'
                                                                    and AreaID like '{2}%' {3}
                                                                    GROUP BY CompanyID_PS
                                                                  ) ReturnMer ON Hos.CompanyID = ReturnMer.CompanyID_PS
                                                 ORDER BY {4} ", Convert.ToDateTime(timeform).ToString(), Convert.ToDateTime(timeto).ToString(), areaid, strwhere, orderby);
			}
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 简报统计1（高值）
		/// </summary> 
		public static DataTable GetReport1(string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计
			string strAreaid = "";
			if (vs.AreaID == "510000")
			{
				strAreaid = vs.AreaID.Substring(0, 2);
			}
			else if (vs.AreaID.EndsWith("00"))
			{
				strAreaid = vs.AreaID.Substring(0, 4);
			}
			else
			{
				strAreaid = vs.AreaID;
			}
			//简报统计1（高值）
			sql = string.Format(@"SELECT  a.AreaName2,a.AreaName,a.HospitalName HospitalName,max(b.Levels1) Levels1,max(b.Levels2) Levels2,cast(sum(PurchaseCount*Price)/10000 AS DECIMAL(18,2)) PurchaseAmount,
                                sum(PurchaseCount) PurchaseCount, sum(CASE WHEN IsGuoChan=0 THEN PurchaseCount ELSE 0 END) JKCount,
                                cast(sum(CASE WHEN IsGuoChan=0 THEN PurchaseCount*Price ELSE 0 END)/10000 AS DECIMAL(18,2)) JKAmount,
                                cast(cast(case when sum(PurchaseCount*Price)=0 then 0 else sum(CASE WHEN IsGuoChan=0 THEN PurchaseCount*Price ELSE 0 END)/sum(PurchaseCount*Price)*100 end as DECIMAL(18,2)) as varchar(50))+'%' JKBL,
                                sum(CASE WHEN IsGuoChan=1 THEN PurchaseCount ELSE 0 END) GCCount,
                                cast(sum(CASE WHEN IsGuoChan=1 THEN PurchaseCount*Price ELSE 0 END)/10000 AS DECIMAL(18,2))  GCAmount,
                                cast(cast(case when sum(PurchaseCount*Price)=0 then 0 else sum(CASE WHEN IsGuoChan=1 THEN PurchaseCount*Price ELSE 0 END)/sum(PurchaseCount*Price)*100 end as DECIMAL(18,2)) as varchar(50))+'%' GCBL,
                                sum(CASE WHEN IsChuanChan=1 THEN PurchaseCount ELSE 0 END) CCCount,
                                cast(sum(CASE WHEN IsChuanChan=1 THEN PurchaseCount*Price ELSE 0 END)/10000 AS DECIMAL(18,2))  CCAmount,
                                cast(cast(case when sum(PurchaseCount*Price)=0 then 0 else sum(CASE WHEN IsChuanChan=1 THEN PurchaseCount*Price ELSE 0 END)/sum(PurchaseCount*Price)*100 end as DECIMAL(18,2)) as varchar(50))+'%' CCBL
                                FROM View_PurchaseOrderDetail a inner join TB_HospitalInfo b on a.HospitalID=b.HospitalID WHERE SubmiTime<'{3}' and SubmiTime>='{2}' AND a.AreaID LIKE '{4}%' and a.HospitalName like '%{0}%'  
                                AND OrderDetailState>0 AND OrderDetailState<>3  GROUP BY a.AreaName2,a.AreaName,a.HospitalName order by {1}", vs.HospitalName, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 简报统计2（高值）
		/// </summary> 
		public static DataTable GetReport2(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计2

			//简报统计2（高值）
			sql = string.Format(@"select*from (select tb1.AreaName,tb1.YSW,isnull(tb2.WSW,0) WSW,tb1.YSW-isnull(tb2.WSW,0) HSW,
 cast(cast(cast(isnull(tb2.WSW,0) as decimal(18,4))/cast(tb1.YSW as decimal(18,4)) as decimal(18,2)) *100 as varchar(50))+'%' WDATE,
  cast(cast(cast((tb1.YSW-isnull(tb2.WSW,0)) as decimal(18,4))/cast(tb1.YSW as decimal(18,4)) as decimal(18,2))*100 as varchar(50))+'%'  YDATE from
  (select d.AreaName AreaName,count(1) YSW  from TB_Hospitalinfo a inner join TB_User b on a.HospitalID=b.UserID inner join 
   TB_Area c on a.AreaID=c.AreaID
inner join TB_Area d on c.FatherAreaID=d.AreaID  where YSW=1 and IsUsing=1  group by d.AreaName)tb1 left join 
(select d.AreaName AreaName,count(distinct HospitalID) WSW  from TB_Hospitalinfo a inner join TB_User b on a.HospitalID=b.UserID inner join 
 TB_Area c on a.AreaID=c.AreaID inner join TB_Area d on c.FatherAreaID=d.AreaID
where HospitalID not in (select HospitalID from View_PurchaseOrderDetail where SubmiTime<'{1}' 
 AND SubmiTime>='{0}' and OrderDetailState<>3) and HospitalID not in 
( select HospitalId from TB_SunScore where SunYear=year(dateadd(month,-1,'{1}')) and SunMonth<=MONTH(dateadd(month,-1,'{1}'))  and IsCaiGou=0 ) 
 and a.YSW=1 and b.IsUsing=1  group by d.AreaName )tb2 on tb1.AreaName=tb2.AreaName)tb order by AreaName", vs.SubmiTimeFrom, vs.SubmiTimeTo);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 简报统计3（高值）
		/// </summary> 
		public static DataTable GetReport3(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计3

			//简报统计3（高值）
			sql = string.Format(@"select HospitalName,c.AreaName AreaName,Levels1,Levels2 from TB_HospitalInfo a
inner join TB_Area c on SUBSTRING(a.AreaID,1,4)+'00'=c.AreaID
inner join TB_User b on a.HospitalID=b.UserID
where HospitalID not in (select HospitalID from View_PurchaseOrderDetail where SubmiTime<'{1}' 
 AND SubmiTime>='{0}'  and OrderDetailState<>3) and HospitalID not in 
 ( select HospitalId from TB_SunScore where SunYear=year(dateadd(month,-1,'{1}')) and SunMonth<=MONTH(dateadd(month,-1,'{1}')) and IsCaiGou=0 )  AND YSW=1
and b.IsUsing=1 order by AreaName", vs.SubmiTimeFrom, vs.SubmiTimeTo);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 简报统计4（高值）
		/// </summary> 
		public static DataTable GetReport4(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计4

			//简报统计4（高值）
			sql = string.Format(@"select GoodsID,RegName,RegCode,sum(PurchaseCount) PurchaseCount,sum(PurchaseCount*Price) PurchaseAmount from View_PurchaseOrderDetail
 where SubmiTime>='{0}' and SubmiTime<'{1}' and OrderDetailState<>3 and OrderDetailState>0 and GoodsID like '%{2}%' and RegName like '%{3}%'
   group by GoodsID,RegName,RegCode order by GoodsID ", vs.SubmiTimeFrom, vs.SubmiTimeTo, vs.GoodsID, vs.RegName);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 简报统计5（高值）
		/// </summary> 
		public static DataTable GetReport5(DateTime SubmiTimeFrom, DateTime SubmiTimeTo, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "", strwhere = "";
			strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计5

			//简报统计5（高值）
			sql = string.Format(@"select*from (select a.GoodsID GoodsID,max(ProductName) ProductName,max(SortName) SortName,
														max(Outlookc) Outlookc,max(PackOutlookc) PackOutlookc,max(PackUnit) PackUnit,
														max(RegCode) RegCode,max(RegName) RegName,max(RegDate) RegDate,
														max(companyName_TB) companyName_TB,max(CompanyName_SC) CompanyName_SC,
														max(OtherArea) OtherArea,max(OtherPrice) OtherPrice,max(OtherTime) OtherTime,
														HospitalName HospitalName,max(Price) Price,sum(PurchaseCount) PurchaseCount,
														sum(PurchaseCount*Price) PurchaseAmount,b.JQPrice JQPrice
												from View_PurchaseOrderDetail a  
													inner join TB_PriceWarnALL b on a.GoodsID=b.GoodsId 
												where  SubmiTime>='{0}' and SubmiTime<'{1}' and OrderDetailState<>3 
													and OrderDetailState>0 and  CountYear=year(dateadd(month,-1,'{1}'))
													and CountMonth=MONTH(dateadd(month,-1,'{1}')) 
												group by a.GoodsID,Price,HospitalName,b.JQPrice)tb 
									where 1=1 {2}  order by GoodsID ", SubmiTimeFrom, SubmiTimeTo, strwhere);
			#endregion
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 简报统计6（高值）
		/// </summary> 
		public static DataTable GetReport6(string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计6
			string strAreaid = "";
			if (vs.AreaID == "510000")
			{
				strAreaid = vs.AreaID.Substring(0, 2);
			}
			else if (vs.AreaID.EndsWith("00"))
			{
				strAreaid = vs.AreaID.Substring(0, 4);
			}
			else
			{
				strAreaid = vs.AreaID;
			}
			//简报统计6（高值）
			sql = string.Format(@"   select *,case when AJQCount>=1 then 1 else 0 end AJQshu,
  case when AHalfCount>=1 then 1 else 0 end AHalfshu,
  case when ACount>=1 then 1 else 0 end Ashu,
  case when BJQCount>=1 then 1 else 0 end BJQshu,
  case when BHalfCount>=1 then 1 else 0 end BHalfshu,
  case when BCount>=1 then 1 else 0 end Bshu from (  select AreaName2,AreaName,AreaID,HospitalName,
 sum(case when CountYear=year('{2}') and CountMonth=month('{2}') and IsMoreJQPrice=1 then 1 else 0 end) AJQCount,
 sum(case when CountYear=year('{2}')  and CountMonth=month('{2}') and IsMorehalf=1 then 1 else 0 end) AHalfCount,
 sum(case when CountYear=year('{2}')  and CountMonth=month('{2}') then 1 else 0 end) ACount,
 sum(case when CountYear=year('{3}') and CountMonth=month('{3}') and IsMoreJQPrice=1 then 1 else 0 end) BJQCount,
 sum(case when CountYear=year('{3}') and CountMonth=month('{3}') and IsMorehalf=1 then 1 else 0 end) BHalfCount,
 sum(case when CountYear=year('{3}') and CountMonth=month('{3}') then 1 else 0 end) BCount,
 sum(case when CountYear=year('{3}') and CountMonth=month('{3}') and IsMoreJQPrice=1 then 1 else 0 end)-
 sum(case when CountYear=year('{2}') and CountMonth=month('{2}') and IsMoreJQPrice=1 then 1 else 0 end) BAJQCha,
  sum(case when CountYear=year('{3}') and CountMonth=month('{3}') and IsMorehalf=1 then 1 else 0 end)-
  sum(case when CountYear=year('{2}') and CountMonth=month('{2}') and IsMorehalf=1 then 1 else 0 end) BAHalfCha,
 sum(case when CountYear=year('{3}') and CountMonth=month('{3}') then 1 else 0 end)-
 sum(case when CountYear=year('{2}') and CountMonth=month('{2}') then 1 else 0 end) BACha 
 from View_HospitalWarn where 1=1 and  AreaID like '{4}%' group by AreaName2,AreaName,HospitalName,AreaID )tb order by {1}", vs.HospitalName, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 简报统计7（高值）
		/// </summary> 
		public static DataTable GetReport7(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{

			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.SuperviseQuery>(vs);
			//strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计7

			//简报统计7（高值）
			string sql = string.Format(@"  select*from TB_GWQK where 1=1  {0} order by AddTime desc", where);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 简报统计8（高值）
		/// </summary> 
		public static DataTable GetReport8(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			string strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 月报统计8
			string strAreaid = "";
			if (vs.AreaID == "510000")
			{
				strAreaid = vs.AreaID.Substring(0, 2);
			}
			else if (vs.AreaID.EndsWith("00"))
			{
				strAreaid = vs.AreaID.Substring(0, 4);
			}
			else
			{
				strAreaid = vs.AreaID;
			}
			//简报统计8（高值）
			sql = string.Format(@"select * from ( select GoodsID,a.AreaID,a.HospitalID,a.HospitalName,HospitalType,Levels1,AreaName2,AreaName,
CompanyName_PS,Price,SubmiTime from  View_PurchaseOrderDetail A inner join TB_Hospitalinfo B 
on A.HospitalID=B.HospitalID  where OrderDetailState>0
 and OrderDetailState<>3 and PurchaseCount<0 and SubmiTime>='{1}' and SubmiTime<='{2}')tb where 1=1 {0}
                        order by SubmiTime", strwhere, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid);
			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 最高价及最低价采购情况
		/// </summary>
		/// <param name="flag"></param> 
		public static DataTable GetReportHL(string str, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format("select * from TB_MaxMinprice where 1=1 {0} order by GoodsID", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(SQL, pagesize);
			pdes.CurrentPage = currentpage;
			totalrecord = pdes.TotalCount;
			DataTable dt = pdes.GetSpecailPage();
			return dt;
		}
		/// <summary>
		/// 最高价采购明细
		/// </summary>
		/// <param name="flag"></param> 
		public static DataTable GetReportH(string str, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format("select a.GoodsID,a.SortName,a.RegName,a.RegCode,a.HospitalName,a.SubmiTime,a.Price from [SCSJ_JY]..View_PurchaseOrderDetail a inner join (select GoodsID GID,MAX(Price) MaxPrice,min(Price) MinPrice from ( select  GoodsID,Price,sum(PurchaseCount) count1  from  View_PurchaseOrderDetail WHERE 1=1 {0} and OrderDetailState<>3 and OrderDetailState>0 group by GoodsID,Price)tb where count1>0 group by GoodsID)b  on a.GoodsID=b.GID where a.Price=b.MaxPrice  and OrderDetailState<>3 and OrderDetailState>0 and PurchaseCount>0  and  1=1 {0} order by MaxPrice desc", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(SQL, pagesize);
			pdes.CurrentPage = currentpage;
			totalrecord = pdes.TotalCount;
			DataTable dt = pdes.GetSpecailPage();
			return dt;
		}
		/// <summary>
		/// 最低价采购明细
		/// </summary>
		/// <param name="flag"></param> 
		public static DataTable GetReportL(string str, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format(" select a.GoodsID,a.SortName,a.RegName,a.RegCode,a.HospitalName,a.SubmiTime,a.Price from  [SCSJ_JY]..View_PurchaseOrderDetail a inner join (select GoodsID GID,MAX(Price) MaxPrice,min(Price) MinPrice from ( select  GoodsID,Price,sum(PurchaseCount) count1  from  View_PurchaseOrderDetail WHERE 1=1 {0} and OrderDetailState<>3 and OrderDetailState>0  group by GoodsID,Price)tb where count1>0 group by GoodsID)b on a.GoodsID=b.GID where a.Price=b.MinPrice  and OrderDetailState<>3 and OrderDetailState>0 and PurchaseCount>0  and 1=1 {0} order by MinPrice desc", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(SQL, pagesize);
			pdes.CurrentPage = currentpage;
			totalrecord = pdes.TotalCount;
			DataTable dt = pdes.GetSpecailPage();
			return dt;
		}
		/// <summary>
		/// 汇总统计的（高值）
		/// </summary> 
		public static DataTable GetStatistics(int flag, string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "", strwhere = "", strflag = "";
			//string isguochan = "";
			strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 医院统计
			if (flag == 1)
			{
				if (!string.IsNullOrEmpty(vs.HospitalName))
				{
					strflag = string.Format("AND CHARINDEX('{0}',HospitalName) > 0 ", vs.HospitalName);
				}


				//医院角度
				sql = string.Format(@"select HospitalID,HospitalName,SUM(PurchaseCount*Price) PurchaseAmount,
											SUM(case when OrderDetailState=2 then Price*PurchaseCount else 0 end) ComResonseAmount,
											SUM(DistributeCount*Price) DistributeAmount,SUM(WarehouseCount*Price) WarehouseAmount,
											CompanyID_PS,CompanyName_PS from View_PurchaseOrderDetail b 
										inner join TB_Role_User a on a.UserID=b.HospitalID
									where A.RoleID = '01' {1} {0} 
										group by HospitalID,HospitalName ,CompanyID_PS,CompanyName_PS  ORDER BY {2}
                                               ", strwhere, strflag, orderby);

			}
			//配送企业汇总
			else if (flag == 2)
			{
				if (!string.IsNullOrEmpty(vs.CompanyName_PS))
				{
					strflag = string.Format(" AND CHARINDEX('{0}',CompanyName_PS) > 0 ", vs.CompanyName_PS);
				}

				sql = string.Format(@"select CompanyID_PS,CompanyName_PS CompanyName,sum(PurchaseCount*Price) PurchaseAmount,
                sum(DistributeCount*Price) DistributeAmount,sum(WarehouseAmount) WarehouseAmount,dbo.fun_GetYearMonth('2014-11-01') AmountTime from 
                   View_PurchaseOrderDetail where OrderDetailState<>3 and OrderDetailState>0  {1} {0}
                      group by CompanyID_PS,CompanyName_PS ORDER BY {2}", strwhere, strflag, orderby);
			}
			//生产企业
			else if (flag == 3)
			{
				if (!string.IsNullOrEmpty(vs.CompanyName_TB))
				{
					strflag = string.Format(" and CompanyName_TB like '%{0}%' ", vs.CompanyName_TB);
				}

				sql = string.Format(@"select CompanyID_TB,companyName_TB CompanyName,sum(PurchaseCount*Price) PurchaseAmount,
                sum(DistributeCount*Price) DistributeAmount,sum(WarehouseAmount) WarehouseAmount from 
                   View_PurchaseOrderDetail where OrderDetailState<>3 and OrderDetailState>0  {1} {0}
                      group by CompanyID_TB,companyName_TB ORDER BY {2}", strwhere, strflag, orderby);
			}
			//地区
            else if (flag == 4)
            {

                sql = string.Format(@"SELECT   AreaID,
		                                    AreaName,
		                                    sum(ISNULL(PurchaseAmount, 0)) PurchaseAmount,  
		                                    sum(ISNULL(DistributeAmount, 0)) DistributeAmount,  
		                                    sum(ISNULL(WarehouseAmount, 0)) WarehouseAmount
	                                        FROM  View_AreaAmount where 1=1 {0} group by AreaID,AreaName ORDER BY {1}
                                               ", strwhere, orderby);
            }
//            //地区
//            else if (flag == 4)
//            {
//                sql = string.Format(@"SELECT Hos.*,
//                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,   
//                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
//                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount
//                                    FROM   ( select AreaID,AreaName from TB_Area where FatherAreaID='510000' 
//                                        ) Hos
//                                        LEFT JOIN ( SELECT left(AreaID,4)+'00' AreaID,
//                                                            SUM(Price * PurchaseCount) PurchaseAmount,
//                                                            SUM(Price*DistributeCount) DistributeAmount,
//                                                            SUM(Price*WarehouseCount) WarehouseAmount
//                                                    FROM    View_PurchaseOrderDetail
//                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and 1=1  {0}
//                                                    GROUP BY left(AreaID,4)
//                                                    ) Pur ON Hos.AreaID = Pur.AreaID ORDER BY {1}
//                                     ", strwhere, orderby);
//            }

			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 汇总统计的（低值）
		/// </summary> 
		public static DataTable GetStatistics_DZ(int flag, string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "", strwhere = "", strflag = "";
			//string isguochan = "";
			strwhere = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(vs, false);
			#region 医院统计
			if (flag == 1)
			{
				if (!string.IsNullOrEmpty(vs.HospitalName))
				{
					strflag = string.Format("AND CHARINDEX('{0}',HospitalName) > 0 ", vs.HospitalName);
				}
				//string strAreaid = "";
				//if (vs.AreaID == "510000")
				//{
				//    strAreaid = vs.AreaID.Substring(0, 2);
				//}
				//else if (vs.AreaID.EndsWith("00"))
				//{
				//    strAreaid = vs.AreaID.Substring(0, 4);
				//}
				//else
				//{
				//    strAreaid = vs.AreaID;
				//}
				//                if (!string.IsNullOrEmpty(vs.IsGuoChan))
				//                {
				//                    isguochan = " and IsGuoChan ='" + vs.IsGuoChan + "'";
				//                }

				//医院角度
				sql = string.Format(@"SELECT HospitalID,
                                        HospitalName,
					                    sum(ISNULL(PurchaseAmount, 0)) PurchaseAmount,  
					                    sum(ISNULL(ComResonseAmount, 0)) ComResonseAmount,  
					                    sum(ISNULL(DistributeAmount, 0)) DistributeAmount,  
					                    sum(ISNULL(WarehouseAmount, 0)) WarehouseAmount
                                FROM      dbo.TB_Role_User A
                                        INNER JOIN dbo.view_HospitalAmountDZ B ON A.UserID = B.HospitalID
                                WHERE     A.RoleID = '01'
                                        AND SNKEY <> '1'
                                        {1} {0} group by HospitalID,HospitalName ORDER BY {2}
                                               ", strwhere, strflag, orderby);
				//                sql = string.Format(@"SELECT Hos.*,
				//                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,  
				//                                                        ISNULL(Pur.ComResonseAmount, 0) ComResonseAmount,  
				//                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
				//                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
				//                                                        ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
				//                                                 FROM   ( SELECT    HospitalID,
				//                                                                    HospitalName
				//                                                          FROM      dbo.TB_Role_User A
				//                                                                    INNER JOIN dbo.TB_Hospital B ON A.UserID = B.HospitalID
				//                                                          WHERE     A.RoleID = '01'
				//                                                                    AND SNKEY <> '1'
				//                                                                    AND AreaID LIKE '{5}%' {1} 
				//                                                        ) Hos
				//                                                        LEFT JOIN ( SELECT  HospitalID,
				//                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
				//                                                                            SUM(CASE WHEN OrderDetailState = 2
				//                                                                                     THEN Price * PurchaseCount
				//                                                                                END) ComResonseAmount,
				//                                                                            SUM(Price*DistributeCount) DistributeAmount,
				//                                                                            SUM(Price*WarehouseCount) WarehouseAmount
				//                                                                    FROM    View_PurchaseOrderDetailDZ
				//                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and 1=1 {0}
				//                                                                    GROUP BY HospitalID
				//                                                                  ) Pur ON Hos.HospitalID = Pur.HospitalID
				//                                                        LEFT JOIN (SELECT  A.HospitalID,
				//                                                                                SUM(RetrunPrice * ReturnCount) ReturnAmount
				//                                                                        FROM    dbo.TB_ReturnMerchandiseDZ A
				//                                                                                INNER JOIN TB_ReturnMerchandiseDetailDZ B ON A.ReturnSerialID = B.ReturnSerialID
				//                                                                                INNER JOIN dbo.TB_Hospital C ON A.HospitalID = C.HospitalID
				//                                                                                inner join TB_ProcureCatalogDZ d on b.ProcureCatalogID =d.ProcureCatalogID
				//							                                                    inner join TB_GoodsDZ e on e.GoodsID = d.ProcureCatalogID
				//                                                                        WHERE   ReturnState = 1
				//                                                                      AND ReturnTime >= '{3}'
				//                                                                      AND ReturnTime <= '{4}'
				//                                                                      AND AreaID LIKE '{5}%' {1} {6}
				//                                                                   GROUP BY A.HospitalID
				//                                                                  ) ReturnMer ON Hos.HospitalID = ReturnMer.HospitalID ORDER BY {2}", strwhere, strflag, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid, isguochan);
			}
			//配送企业汇总
			else if (flag == 2)
			{
				if (!string.IsNullOrEmpty(vs.CompanyName_PS))
				{
					strflag = string.Format(" AND CHARINDEX('{0}',CompanyName_PS) > 0 ", vs.CompanyName_PS);
				}
				//string strAreaid = "";
				//if (vs.AreaID == "510000")
				//{
				//    strAreaid = vs.AreaID.Substring(0, 2);
				//}
				//else if (vs.AreaID.EndsWith("00"))
				//{
				//    strAreaid = vs.AreaID.Substring(0, 4);
				//}
				//else
				//{
				//    strAreaid = vs.AreaID;
				//}
				sql = string.Format(@"select CompanyID_PS,CompanyName_PS CompanyName,sum(PurchaseCount*Price) PurchaseAmount,
                sum(DistributeCount*Price) DistributeAmount,sum(WarehouseAmount) WarehouseAmount from 
                   View_PurchaseOrderDetailDZ where OrderDetailState<>3 and OrderDetailState>0  {1} {0}
                      group by CompanyID_PS,CompanyName_PS ORDER BY {2}
                                               ", strwhere, strflag, orderby);
				//                sql = string.Format(@"SELECT Hos.*,
				//                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,   
				//                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
				//                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
				//                                                        ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
				//                                                 FROM   ( SELECT    CompanyID,
				//                                                            CompanyName
				//                                                  FROM      TB_CompanyInfo A INNER JOIN dbo.TB_Role_User B
				//                                                  ON A.CompanyID = B.UserID
				//                                                  WHERE 1=1 and B.RoleID IN ('06','02')  {1}
				//                                                        ) Hos
				//                                                        LEFT JOIN ( SELECT  CompanyID_PS,
				//                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
				//                                                                            SUM(Price*DistributeCount) DistributeAmount,
				//                                                                            SUM(Price*WarehouseCount) WarehouseAmount
				//                                                                    FROM    View_PurchaseOrderDetailDZ 
				//                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and 1=1  {0}
				//                                                                    GROUP BY CompanyID_PS
				//                                                                  ) Pur ON Hos.CompanyID = Pur.CompanyID_PS
				//                                                        LEFT JOIN ( 
				//                                                                            SELECT  B.CompanyID,
				//                                                                                SUM(RetrunPrice * ReturnCount) ReturnAmount
				//                                                                        FROM    dbo.TB_ReturnMerchandiseDZ A
				//                                                                                INNER JOIN TB_ReturnMerchandiseDetailDZ B ON A.ReturnSerialID = B.ReturnSerialID
				//                                                                                INNER JOIN dbo.TB_Hospital C ON a.HospitalID = C.HospitalID
				//                                                                        WHERE   ReturnState = 1 AND AreaID LIKE '{5}%' 
				//                                                                        AND ReturnTime >= '{3}'
				//                                                                       AND ReturnTime <= '{4}'
				//                                                                   GROUP BY B.CompanyID
				//                                                                  ) ReturnMer ON Hos.CompanyID = ReturnMer.CompanyID ORDER BY {2}", strwhere, strflag, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid);
			}
			//生产企业
			else if (flag == 3)
			{
				if (!string.IsNullOrEmpty(vs.CompanyName_SC))
				{
					strflag = string.Format(" and CompanyName_SC like '%{0}%' ", vs.CompanyName_SC);
				}
				//string strAreaid = "";
				//if (vs.AreaID == "510000")
				//{
				//    strAreaid = vs.AreaID.Substring(0, 2);
				//}
				//else if (vs.AreaID.EndsWith("00"))
				//{
				//    strAreaid = vs.AreaID.Substring(0, 4);
				//}
				//else
				//{
				//    strAreaid = vs.AreaID;
				//}
				sql = string.Format(@"select CompanyName_SC CompanyName,sum(PurchaseCount*Price) PurchaseAmount,
                sum(DistributeCount*Price) DistributeAmount,sum(WarehouseAmount) WarehouseAmount from 
                   View_PurchaseOrderDetailDZ where OrderDetailState<>3 and OrderDetailState>0  {1} {0}
                      group by companyName_SC ORDER BY  {2}
                                               ", strwhere, strflag, orderby);
				//                sql = string.Format(@"SELECT Hos.*,
				//                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,   
				//                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
				//                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
				//                                                         ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
				//                                                 FROM   ( SELECT DISTINCT CompanyID,CompanyName  
				//		                                                        FROM dbo.TB_Company A INNER JOIN TB_GoodsDZ B
				//                                                                 ON A.CompanyID = B.CompanyID_TB WHERE 1=1 {1}
				//                                                        ) Hos
				//                                                        LEFT JOIN ( SELECT  CompanyID_TB,
				//                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
				//                                                                            SUM(Price*DistributeCount) DistributeAmount,
				//                                                                            SUM(Price*WarehouseCount) WarehouseAmount
				//                                                                    FROM    View_PurchaseOrderDetailDZ
				//                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and 1=1  {0}
				//                                                                    GROUP BY CompanyID_TB
				//                                                                  ) Pur ON Hos.CompanyID = Pur.CompanyID_TB
				//                                                        LEFT JOIN ( SELECT  CompanyID_TB,
				//                                                                                    SUM(RetrunPrice
				//                                                                                        * ReturnCount) ReturnAmount
				//                                                                            FROM    dbo.TB_ReturnMerchandiseDZ A
				//                                                                                    INNER JOIN TB_ReturnMerchandiseDetailDZ B ON A.ReturnSerialID = B.ReturnSerialID
				//                                                                                    INNER JOIN dbo.TB_GoodsDZ C ON B.ProcureCatalogID = C.GoodsID
				//                                                                                    INNER JOIN dbo.TB_Hospital D ON A.HospitalID = D.HospitalID
				//                                                                                    WHERE 1=1 AND AreaID LIKE '{5}%' and ReturnState =1
				//                                                                                    AND ReturnTime >= '{3}'
				//                                                                                    AND ReturnTime <= '{4}'
				//                                                                            GROUP BY CompanyID_TB
				//                                                                  ) ReturnMer ON Hos.CompanyID = ReturnMer.CompanyID_tb ORDER BY {2}", strwhere, strflag, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid);

			}
			//地区
			else if (flag == 4)
			{
				//string strAreaid = "";
				//if (vs.AreaID == "510000")
				//{
				//    strAreaid = vs.AreaID.Substring(0, 2);
				//}
				//else if (vs.AreaID.EndsWith("00"))
				//{
				//    strAreaid = vs.AreaID.Substring(0, 4);
				//}
				//else
				//{
				//    strAreaid = vs.AreaID;
				//}
				//if (!string.IsNullOrEmpty(vs.IsGuoChan))
				//{
				//    isguochan = " and IsGuoChan ='" + vs.IsGuoChan + "'";
				//}
				sql = string.Format(@"SELECT   AreaID,
		                                    AreaName,
		                                    sum(ISNULL(PurchaseAmount, 0)) PurchaseAmount,  
		                                    sum(ISNULL(DistributeAmount, 0)) DistributeAmount,  
		                                    sum(ISNULL(WarehouseAmount, 0)) WarehouseAmount
	                                        FROM  View_AreaAmountDZ where 1=1 {0} group by AreaID,AreaName ORDER BY {1}
                                               ", strwhere, orderby);
				//                sql = string.Format(@"SELECT Hos.*,
				//                                                        ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,   
				//                                                        ISNULL(Pur.DistributeAmount, 0) DistributeAmount,  
				//                                                        ISNULL(Pur.WarehouseAmount, 0) WarehouseAmount, 
				//                                                         ISNULL(ReturnMer.ReturnAmount, 0) ReturnAmount 
				//                                                 FROM   ( select AreaID,AreaName from TB_Area where FatherAreaID='510000' and  AreaID LIKE '{5}%'
				//                                                        ) Hos
				//                                                        LEFT JOIN ( SELECT left(AreaID,4)+'00' AreaID,
				//                                                                            SUM(Price * PurchaseCount) PurchaseAmount,
				//                                                                            SUM(Price*DistributeCount) DistributeAmount,
				//                                                                            SUM(Price*WarehouseCount) WarehouseAmount
				//                                                                    FROM    View_PurchaseOrderDetailDZ
				//                                                                    WHERE   PurchaseCount>0 and OrderDetailState > 0 and 1=1  {0}
				//                                                                    GROUP BY left(AreaID,4)
				//                                                                  ) Pur ON Hos.AreaID = Pur.AreaID
				//                                                        LEFT JOIN ( SELECT  left(AreaID,4)+'00' AreaID ,
				//                                                        SUM(RetrunPrice
				//                                                            * ReturnCount) ReturnAmount
				//                                                 FROM    dbo.TB_ReturnMerchandiseDZ A
				//                                                        INNER JOIN TB_ReturnMerchandiseDetailDZ B ON A.ReturnSerialID = B.ReturnSerialID
				//                                                        INNER JOIN dbo.TB_GoodsDZ C ON B.ProcureCatalogID = C.GoodsID
				//                                                        INNER JOIN dbo.TB_Hospital D ON A.HospitalID = D.HospitalID
				//                                                        WHERE 1=1 AND AreaID LIKE '{5}%' and ReturnState =1 {6}
				//                                                        AND ReturnTime >= '{3}'
				//                                                        AND ReturnTime <= '{4}'
				//                                                GROUP BY left(AreaID,4)
				//                                                                  ) ReturnMer ON Hos.AreaID = ReturnMer.AreaID ORDER BY {2}", strwhere, strflag, orderby, vs.SubmiTimeFrom, vs.SubmiTimeTo, strAreaid, isguochan);
			}

			#endregion

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看医院订单明细
		/// </summary>
		/// <param name="str">查询条件</param>
		/// <returns>dt</returns>
        public static DataTable GetHospitalStatisticsMX(string orderby, string where, int currentPage, int pageSize, out int totalRecord)
		{
            ////case when IsChuanChan=1 then '川企' when IsChuanChan=0 then '省外' when IsChuanChan=2 then '投川省外' when IsChuanChan=3 then '川企省外' else '' end IsCC 
            string sql = String.Format(@" select GoodsId,ProductName,Outlookc,PackUnit,HospitalName,CompanyName_SC,CompanyName_TB,CompanyName_PS,SortName,PurchaseCount,SubmiTime,OrderName,
                                          Price*PurchaseCount PurchaseAmount,Price*DistributeCount DistributeAmount 
                                          from View_PurchaseOrderDetail where OrderDetailState>0 {0} ", where, orderby); ////order by {1}
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
		}
        /// <summary>
        /// 统计医院订单明细
        /// </summary>
        /// <param name="str">查询条件</param>
        /// <returns>dt</returns>
        public static DataTable GetHospitalStatisticsCount(string where)
        {
            string sql = String.Format(@" select count(distinct HospitalName) HospitalCount,count(distinct CompanyName_PS) CompanyCount,count(distinct GoodsId) GoodsCount,sum(Price*PurchaseCount) Amount from View_PurchaseOrderDetail where OrderDetailState>0 {0}", where);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
		/// <summary>
		/// 查看医疗机构大型设备采购合同
		/// </summary> 
		public static DataTable GetOrderDXSB(HZ.Supervise.Model.Query.QOrderDXSB query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.QOrderDXSB>(query);
			string sql = string.Format(@"select *,case when Orderlink IS NULL or LEN(Orderlink)<1 then '未上传' else '查看' end IsUp from VIEW_OrderDXSB where 1=1 {0}", where);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		public static DataTable GetOrderDXSB2(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select * from TB_OrderDXSB where 1=1 {0}", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		/// <summary>
		/// 获取分类名称
		/// </summary>  
		public static DataTable GetSortNameDXSB()
		{
			string sql = "select distinct SortName from TB_Goods  order by SortName;";
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}
		/// <summary>
		/// 医疗机构获取合同内明细
		/// </summary> 
		public static DataTable GetOrderDetailDXSB(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"select * from View_DeailGoodsDXSB where 1=1 {0}", where);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		/// <summary>
		/// 获取单个合同内的明细
		/// </summary>  
		public static DataTable GetOneDetailDXSB(string str)
		{
			string sql = string.Format(@" select * from TB_PurchasedetailDXSB where 1=1 {0}", str);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}
		/// <summary>
		/// 中心获取所有明细
		/// </summary> 
		public static DataTable GetAllDetailDXSB2(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"select*from View_PurchasedetailDXSB where 1=1 {0} ", where);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		/// <summary>
		/// 获取类别采购信息
		/// </summary>          
		/// <summary>
		public static DataTable GetAllPurchaseSummary(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"select  SortName,sum(Purchasecount) sumpur from View_PurchasedetailDXSB where 1=1 {0} group by  SortName order by SortName", where);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		/// <summary>
		/// 获取医疗机构信息
		/// </summary>          
		/// <summary>
		public static DataTable GetAllPurchaseSummaryTotal(HZ.Supervise.Model.Query.QGoodsDXSB query)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"select COUNT(distinct HospitalID) zs from View_PurchasedetailDXSB where OrderDetailState=1 and 1=1 {0} ", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}
		public static DataTable GetAllPurchaseSummaryTotal2(HZ.Supervise.Model.Query.QGoodsDXSB query)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql2 = string.Format(@"select COUNT(distinct HospitalID) sj from View_PurchasedetailDXSB where OrderDetailState=1 and levels2='三级' and 1=1 {0} ", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql2);
		}
		public static DataTable GetAllPurchaseSummaryTotal3(HZ.Supervise.Model.Query.QGoodsDXSB query)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql3 = string.Format(@"select COUNT(distinct HospitalID) ej from View_PurchasedetailDXSB where OrderDetailState=1 and levels2='二级' and 1=1 {0} ", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql3);
		}
		public static DataTable GetAllPurchaseSummaryTotal4(HZ.Supervise.Model.Query.QGoodsDXSB query)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql4 = string.Format(@"select COUNT(distinct HospitalID) ejyx from View_PurchasedetailDXSB where OrderDetailState=1 and levels2='二级以下' and 1=1 {0} ", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql4);
		}

		/// <summary>
		/// 获取裸机价格信息
		/// </summary>          
		/// <summary>
		public static DataTable GetBarepriceGT(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"
                                            select SortName,
                                            count(distinct GoodsID) cpzs ,
                                            sum(case when RegCode not like '%进%' then  Purchasecount else 0 end) sumgc,
                                            case when sum(Purchasecount)=0 then 0 else cast(cast(sum(case when RegCode not like '%进%' then  Purchasecount else 0 end) as decimal(18,4))/cast(sum(Purchasecount) as decimal(18,4)) as decimal(18,4)) end gcbl,
                                            sum(case when RegCode like '%进%' then  Purchasecount else 0 end) sumjk,
                                            case when sum(Purchasecount)=0 then 0 else cast(cast(sum(case when RegCode  like '%进%' then  Purchasecount else 0 end) as decimal(18,4))/cast(sum(Purchasecount) as decimal(18,4)) as decimal(18,4)) end jkbl,
                                            sum(Purchasecount) sumzs
                                            from View_PurchasedetailDXSB where 1=1 {0} 
                                            group by SortName order by sumzs desc 
                                           ", where);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}
		public static DataTable GetBarepriceGTTotal(HZ.Supervise.Model.Query.QGoodsDXSB query)
		{
			string where = DAL_Helper.GetWhereCondition2<HZ.Supervise.Model.Query.QGoodsDXSB>(query);
			string sql = string.Format(@"
                                        select 
                                        count(distinct GoodsID) cpzs ,
                                        sum(case when RegCode not like '%进%' then  Purchasecount else 0 end) sumgc,
                                        case when sum(Purchasecount)=0 then 0 else cast(cast(sum(case when RegCode not like '%进%' then  Purchasecount else 0 end) as decimal(18,4))/cast(sum(Purchasecount) as decimal(18,4)) as decimal(18,4)) end gcbl,
                                        sum(case when RegCode like '%进%' then  Purchasecount else 0 end) sumjk,
                                        case when sum(Purchasecount)=0 then 0 else cast(cast(sum(case when RegCode  like '%进%' then  Purchasecount else 0 end) as decimal(18,4))/cast(sum(Purchasecount) as decimal(18,4)) as decimal(18,4)) end jkbl,
                                        sum(Purchasecount) sumzs
                                        from View_PurchasedetailDXSB where 1=1 {0} 
                                        ", where);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}

		/// <summary>
		/// 获取地区下的医疗机构
		/// </summary>
		/// <param name="vs"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalByAreaId(string where, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format(@"select * from TB_Hospital where 1=1 {0} order by HospitalID", where);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 获取阳光积分
		/// </summary>
		/// <param name="vs"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetSunScore(string where, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format(@"select a.*,b.HospitalName,AreaName,b.AreaID,c.Levels2,case when a.IsCaiGou=1 then '是' else '否' end IsCaiGouBL,case when a.IsOnWebBuy=1 then '是' else '否' end IsOnWebBuyBL,case when a.isAllGuaWang=1 then '是' else '否' end isAllGuaWangBL from TB_SunScore a
inner join TB_Hospital b on a.HospitalId = b.HospitalID inner join TB_HospitalInfo c on b.HospitalID=c.HospitalID  where 1=1 {0} order by SunYear,SunMonth desc", where);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看整改情况
		/// </summary>
		public static DataTable GetWarnword(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select * from TB_Warnword where 1=1 {0}", str);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);
			des.CurrentPage = currentPage;
			totalRecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看预警月
		/// </summary>
		public static DataTable GetWarnwords(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select *,case when UrlDZ IS NULL or LEN(UrlDZ)<1 then '未上传' else '查看' end IsUp  from view_Warnword where 1=1  {0} order by CountYear,CountMonth desc", str);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);
			des.CurrentPage = currentPage;
			totalRecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 查看预警数据
		/// </summary>
		public static DataTable GetHospitalWarn(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select * from View_HospitalWarn where 1=1 {0} order by CountYear,CountMonth desc", str);
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pageSize);
			des.CurrentPage = currentPage;
			totalRecord = des.TotalCount;
			return des.GetSpecailPage();
		}
		/// <summary>
		/// 更新提交状态
		/// </summary>
		/// <returns></returns>
		public static int WarnwordsSubmit(string Urlid, int flag)
		{
			HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
			try
			{
				sh.BeginTrans();
				string sql = string.Format("update TB_Warnword set SubStatus={1},SubmiTime='{2}' where Urlid in ('{0}')", Urlid, flag, DateTime.Now);
				HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
				sh.CommitTrans();
				return 1;
			}
			catch (Exception ex)
			{
				sh.RollTrans();
				return 0;
			}
		}

		/// <summary>
		/// 查看医疗机构阳光积分
		/// </summary>
		public static DataTable GetSunMark(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select a.*,b.HospitalName,AreaName,b.AreaID,Levels2,case when a.IsCaiGou=1 then '是' else '否' end IsCaiGouBL from TB_SunScore a
inner join TB_Hospital b on a.HospitalId = b.HospitalID inner join TB_HospitalInfo c on b.HospitalID=c.HospitalID where 1=1 {0} ", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}

		/// <summary>
		/// 医院积分情况统计
		/// </summary>
		public static DataTable GetHospitalScoreStatistics(string str, int currentPage, int pageSize, out int totalRecord)
		{
			string sql = string.Format(@"select E.AreaName AreaNameSZ,D.AreaName AreaNameQX,B.UserName HosName,
                                                CASE WHEN  C.YSW=1 THEN '是' else '否' end YSW,
                                                SUM(CASE WHEN SunMonth=1 THEN MonthScore ELSE NULL END) Y1,
                                                SUM(CASE WHEN SunMonth=2 THEN MonthScore ELSE null END) Y2,
                                                SUM(CASE WHEN SunMonth=3 THEN MonthScore ELSE null END) Y3,
                                                SUM(CASE WHEN SunMonth=4 THEN MonthScore ELSE null END) Y4,
                                                SUM(CASE WHEN SunMonth=5 THEN MonthScore ELSE null END) Y5,
                                                SUM(CASE WHEN SunMonth=6 THEN MonthScore ELSE null END) Y6,
                                                SUM(CASE WHEN SunMonth=7 THEN MonthScore ELSE NULL END) Y7,
                                                SUM(CASE WHEN SunMonth=8 THEN MonthScore ELSE null END) Y8,
                                                SUM(CASE WHEN SunMonth=9 THEN MonthScore ELSE null END) Y9,
                                                SUM(CASE WHEN SunMonth=10 THEN MonthScore ELSE null END) Y10,
                                                SUM(CASE WHEN SunMonth=11 THEN MonthScore ELSE null END) Y11,
                                                SUM(CASE WHEN SunMonth=12 THEN MonthScore ELSE null END) Y12,
                                                AVG(MonthScore) AVGScore from 
                                                TB_SunScore A INNER JOIN View_HospitalUsers B ON A.HospitalId=B.UserID INNER JOIN TB_HospitalInfo C ON
                                                B.UserID=C.HospitalID inner join TB_Area D on c.AreaID=d.AreaID inner join TB_Area E on D.FatherAreaID=E.AreaID 
                                                where 1=1 {0} AND Levels2 in ('二级','三级') AND MonthScore<>0
                                                GROUP BY  E.AreaName,D.AreaName,B.UserName,C.YSW order by AVGScore", str);
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;
			return pdes.GetSpecailPage();
		}

		/// <summary>
		/// 获取一个医院的积分
		/// </summary>
		/// <param name="hospitalid"></param>
		/// <param name="year"></param>
		/// <param name="month"></param>
		/// <returns></returns>
		public static DataTable GetSunScoreByHospital(string hospitalid, int year, int month)
		{
			string sql = string.Format(@"select * from TB_SunScore where HospitalId='{0}' and SunYear ='{1}' and SunMonth='{2}'", hospitalid, year, month);
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}

		/// <summary>
		/// 新增一个阳光积分数据
		/// </summary>
		/// <param name="sun"></param>
		/// <returns></returns>
		public static int InsertSunScore(SunScore sun)
		{
			string sql = string.Format(@"insert into TB_SunScore 
                                        (HospitalId,SunYear,SunMonth,IsShiGu,IsPeiHe,
                                        NoSame,NoJianDu,IsShouHui,ShouHuiReason,IsZhiXing,
                                        NoBackMoney,WrongPut)
                                         values
                                         ('{0}','{1}','{2}','{3}','{4}',
                                         '{5}','{6}','{7}','{8}','{9}',
                                         '{10}','{11}')", sun.HospitalId, sun.SunYear, sun.SunMonth, sun.IsShiGu, sun.IsPeiHe, sun.NoSame, sun.NoJianDu, sun.IsShouHui, sun.ShouHuiReason, sun.IsZhiXing, sun.NoBackMoney, sun.WrongPut);
			return (int)HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
		}

		/// <summary>
		/// 修改一个阳光积分
		/// </summary>
		/// <param name="sun"></param>
		/// <returns></returns>
		public static int UpdateSunScore(SunScore sun)
		{
			string sql = string.Format(@"update TB_SunScore set IsShiGu='{0}' ,IsPeiHe='{1}' ,
NoSame='{2}' ,NoJianDu='{3}' ,IsShouHui='{4}' ,ShouHuiReason='{5}' ,IsZhiXing='{6}' ,isAllGuaWang='{12}',
NoBackMoney='{7}' ,WrongPut='{8}'  where HospitalId='{9}' and SunYear='{10}' and SunMonth='{11}' ", sun.IsShiGu, sun.IsPeiHe, sun.NoSame, sun.NoJianDu, sun.IsShouHui, sun.ShouHuiReason, sun.IsZhiXing, sun.NoBackMoney, sun.WrongPut, sun.HospitalId, sun.SunYear, sun.SunMonth, sun.isAllGuaWang);
			return (int)HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
		}

		/// <summary>
		/// todo：汇总金额类别统计
		/// JOHN
		/// </summary>
		/// <returns>汇总金额类别统计数据源</returns>
		public static System.Data.DataTable GetAmountType(string str)
		{
			string SQL = string.Format(@"SELECT * FROM dbo.View_Sum");

			return HZ.Data.DbHelperFactory.Create().ExecuteTable(SQL);
		}

		/// <summary>
		/// todo：汇总金额，按照月份
		/// JOHN
		/// </summary>
		/// <returns></returns>
		public static System.Data.DataTable GetAmountByMonth(string str, int currentpage, int pagesize, out int totalrecord)
		{
			string SQL = string.Format(@"select * from View_Sum_Detail where 1=1 {0} order by id", str);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 超过12小时未响应的药品
		/// </summary>
		/// <param name="pro"></param>
		/// <returns></returns>
		public static DataTable Get12HourByProcureCatalog(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strwhere = "";
			SuperviseQuery sqtmp = sq;
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeFrom)))
			{
				strwhere += " AND CenSubmiitTime >='" + sq.CenSubmiitTimeFrom + "'";
				sq.CenSubmiitTimeFrom = null;

			}

			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeTo)))
			{
				strwhere += " AND CenSubmiitTime <'" + sq.CenSubmiitTimeTo + "'";
				sq.CenSubmiitTimeTo = null;

			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.AreaID)))
			{
				strwhere += sq.AreaID.Replace("AreaID", "pur.AreaID");
				sq.AreaID = null;
			}
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				strwhere += string.Format(" AND Levels IN ({0})", sq.Levels);
				sq.Levels = null;
			}



			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				strwhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
				sq.IsCountry = null;

			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				strwhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
				sq.IsGovernment = null;

			}


			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(sq, true);

			string SQL = string.Format(@"SELECT  *
                                            FROM (  SELECT    pur.ProcureCatalogID ,
                                                                MAX(pur.ProductName) ProductName ,
                                                                MAX(pur.PinyinCode) PinyinCode,
                                                                MAX(pur.MedicineModelName) MedicineModelName ,
                                                                MAX(pur.Outlookc) Outlookc ,
                                                                MAX(pur.Factor) Factor ,
                                                                MAX(pur.Pack) Pack ,
                                                                MAX(pur.Unit) Unit ,
                                                                MAX(pur.CompanyName_SC) CompanyName_SC ,
                                                                MAX(pur.CompanyID_SC) CompanyID_SC ,
                                                                MAX(ProcurePrice) ProcurePrice,
                                                                SUM(pur.PurchaseCount)PurchaseCount,
                                                                MAX(tb_sort.SortName) SortName,
                                                                MAX(tb_sort.SortID) SortID,
                                                                COUNT(*) OrderCount
                                                      FROM      dbo.View_PurchaseOrderDetail pur
                                                                LEFT JOIN dbo.TB_ProcureCatalogSort sort ON pur.ProcureCatalogID = sort.ProcureCatalogID
                                                                LEFT JOIN dbo.TB_Hospital hos ON hos.HospitalID = pur.HospitalID
                                                                LEFT JOIN dbo.TB_Sort ON dbo.TB_Sort.SortID=sort.SortID
                                                      WHERE     sort.SortID IN (select sortid from tb_sort)
                                                                AND OrderDetailState > 0
                                                                AND DATEDIFF(HOUR, CenSubmiitTime, ISNULL(ComResponseTime,GETDATE())) > 12 {0}
                                                      GROUP BY  pur.ProcureCatalogID
                                                    ) AA
                                            WHERE   1 = 1 {1} ", strwhere, where);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 超过12小时未配送的企业列表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable Get12HourByCompany(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			sq.OrderName = null;
			sq.HospitalName = null;
			string strWhere = get12HourWhere(sq);
			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);
			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
			}

			string SQL = string.Format(@"SELECT CompanyID_PS ,
                                                CompanyName_PS ,
                                                SUM(PurchaseCount) PurchaseCount,
                                                COUNT(*) OrderCount
                                         FROM   dbo.TB_PurchaseOrderDetail
                                         WHERE  OrderDetailState > 0
                                                AND DATEDIFF(HOUR, CenSubmiitTime, ISNULL(ComResponseTime,GETDATE())) > 12
                                                AND HospitalID IN (SELECT HospitalID FROM dbo.TB_Hospital WHERE 1=1 {2})
                                                {0} 
                                         GROUP BY CompanyID_PS ,
                                                CompanyName_PS ORDER BY {1}", strWhere, sq.SortFields, hosWhere);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 超过12小时未配送的采购详情
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable Get12HourByPurchase(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strWhere = get12HourWhere(sq);
			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);
			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
			}
			string SQL = string.Format(@"SELECT  *
                                            FROM    ( SELECT    pur.OrderName ,
                                                                pur.HospitalName ,
                                                                pur.Price ,
                                                                pur.PurchaseCount ,
                                                                pur.CenSubmiitTime ,
                                                                pur.ComResponseTime ,
                                                                pur.AreaID ,
                                                                pur.OrderDetailState ,
                                                                pur.ProcureCatalogID ,
                                                                pur.CompanyName_PS ,
                                                                pur.CompanyID_PS ,
                                                                pur.OrderDetailID ,
                                                                CASE WHEN pur.OrderDetailState = 2 THEN '确认配送'
                                                                     WHEN pur.OrderDetailState = '3' THEN '拒绝配送'
                                                                     ELSE '未响应'
                                                                END OrderState ,
                                                                DATEDIFF(HOUR, CenSubmiitTime,
                                                                         ISNULL(ComResponseTime, GETDATE())) - 12 OutnumberHour ,
                                                                dis.DistributeTime,hos.IsCountry,hos.Levels,hos.IsGovernment
                                                      FROM      dbo.View_PurchaseOrderDetail pur
                                                                LEFT JOIN dbo.TB_Distribution dis ON pur.OrderDetailID = dis.OrderDetailID
                                                                LEFT JOIN dbo.TB_Hospital hos ON pur.HospitalID = hos.HospitalID
                                                    ) AA
                                            WHERE   OrderDetailState > 0
                                                    AND DATEDIFF(HOUR, CenSubmiitTime, ISNULL(ComResponseTime, GETDATE())) > 12
                                                    {0} ORDER BY {1}", strWhere + hosWhere, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		public static string get12HourWhere(SuperviseQuery sq)
		{

			string strWhere = "";
			if (!string.IsNullOrEmpty(sq.AreaID))
			{
				strWhere += sq.AreaID;
			}
			if (!string.IsNullOrEmpty(sq.ProcureCatalogID))
			{
				strWhere += "AND ProcureCatalogID = '" + sq.ProcureCatalogID + "' ";
			}
			if (!string.IsNullOrEmpty(sq.CompanyName_PS))
			{
				strWhere += " AND CompanyName_PS LIKE '%" + sq.CompanyName_PS + "%'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeFrom)))
			{
				strWhere += " AND CenSubmiitTime >='" + sq.CenSubmiitTimeFrom + "'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeTo)))
			{
				strWhere += " AND CenSubmiitTime < '" + sq.CenSubmiitTimeTo + "'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.OrderName)))
			{
				strWhere += " AND OrderName LIKE '%" + sq.OrderName + "%'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.HospitalName)))
			{
				strWhere += " AND HospitalName LIKE '%" + sq.HospitalName + "%'";
			}
			if (!string.IsNullOrEmpty(sq.CompanyID_PS))
			{
				strWhere += " AND CompanyID_PS ='" + sq.CompanyID_PS + "'";
			}

			return strWhere;
		}

		/// <summary>
		/// 超过24小时未响应的药品
		/// </summary>
		/// <param name="pro"></param>
		/// <returns></returns>
		public static DataTable Get24HourByProcureCatalog(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strwhere = "";
			SuperviseQuery sqtmp = sq;
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeFrom)))
			{
				strwhere += " AND ComResponseTime >='" + sq.CenSubmiitTimeFrom + "'";
				sq.CenSubmiitTimeFrom = null;

			}

			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeTo)))
			{
				strwhere += " AND ComResponseTime <'" + sq.CenSubmiitTimeTo + "'";
				sq.CenSubmiitTimeTo = null;

			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.AreaID)))
			{
				strwhere += sq.AreaID.Replace("AreaID", "pur.AreaID");
				sq.AreaID = null;
			}
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				strwhere += string.Format(" AND Levels IN ({0})", sq.Levels);
				sq.Levels = null;
			}

			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				strwhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
				sq.IsCountry = null;
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				strwhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
				sq.IsGovernment = null;
			}
			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.SuperviseQuery>(sq);
			string SQL = string.Format(@"SELECT *
                                             FROM   ( SELECT    pur.ProcureCatalogID ,
                                                                MAX(pur.ProductName) ProductName ,
                                                                MAX(pur.PinyinCode) PinyinCode,
                                                                MAX(pur.MedicineModelName) MedicineModelName ,
                                                                MAX(pur.Outlookc) Outlookc ,
                                                                MAX(pur.Factor) Factor ,
                                                                MAX(pur.Pack) Pack ,
                                                                MAX(pur.Unit) Unit ,
                                                                MAX(pur.CompanyName_SC) CompanyName_SC ,
                                                                MAX(pur.CompanyID_SC) CompanyID_SC ,
                                                                MAX(ProcurePrice) ProcurePrice,
                                                                MAX(tb_sort.SortName) SortName,
                                                                MAX(tb_sort.SortID) SortID,
                                                                SUM(pur.PurchaseCount)PurchaseCount,
                                                                COUNT(*) OrderCount
                                                      FROM      dbo.View_PurchaseOrderDetail pur
                                                                LEFT JOIN dbo.TB_Distribution dis ON pur.OrderDetailID = dis.OrderDetailID
                                                                LEFT JOIN dbo.TB_ProcureCatalogSort sort ON pur.ProcureCatalogID = sort.ProcureCatalogID
                                                                LEFT JOIN dbo.TB_Hospital hos ON pur.HospitalID = hos.HospitalID
                                                                LEFT JOIN dbo.TB_Sort ON dbo.TB_Sort.SortID=sort.SortID
                                                      WHERE     pur.OrderDetailState = 2
                                                                AND DATEDIFF(HOUR, ComResponseTime,
                                                                             CASE WHEN dis.DistributeTime IS NULL
                                                                                  THEN GETDATE()
                                                                                  ELSE dis.DistributeTime
                                                                             END) > 24
                                                                AND sort.SortID IN ( select sortid from tb_sort) {0}
                                                      GROUP BY  pur.ProcureCatalogID
                                                    ) AA
                                             WHERE  1 = 1   {1} ", strwhere, where);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 超过24小时未配送的企业列表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable Get24HourByCompany(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			sq.OrderName = null;
			sq.HospitalName = null;
			string where = get24HourWhere(sq);
			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);
			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
			}
			string SQL = string.Format(@"SELECT pur.CompanyID_PS ,
                                                pur.CompanyName_PS,
                                                SUM(pur.PurchaseCount) PurchaseCount,
                                                COUNT(*) OrderCount
                                             FROM   dbo.TB_PurchaseOrderDetail pur
                                                    LEFT JOIN dbo.TB_Distribution dis ON pur.OrderDetailID = dis.OrderDetailID
                                                    LEFT JOIN dbo.TB_Hospital ON pur.HospitalID = dbo.TB_Hospital.HospitalID
                                             WHERE  pur.OrderDetailState = 2
                                                    AND DATEDIFF(HOUR, ComResponseTime,
                                                                 CASE WHEN dis.DistributeTime IS NULL THEN GETDATE()
                                                                      ELSE dis.DistributeTime
                                                                 END) > 24 {0}
                                             GROUP BY pur.CompanyID_PS ,
                                                    pur.CompanyName_PS ORDER BY {1}", where, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		public static string get24HourWhere(SuperviseQuery sq)
		{
			string where = "";
			if (!string.IsNullOrEmpty(sq.ProcureCatalogID))
			{
				where = "AND pur.ProcureCatalogID = '" + sq.ProcureCatalogID + "'";
			}
			if (!string.IsNullOrEmpty(sq.CompanyName_PS))
			{
				where += " AND pur.CompanyName_PS LIKE '%" + sq.CompanyName_PS + "%'";
			}
			if (!string.IsNullOrEmpty(sq.AreaID))
			{
				where += sq.AreaID.Replace("AreaID", "pur.AreaID");
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeFrom)))
			{
				where += " AND pur.ComResponseTime >='" + sq.CenSubmiitTimeFrom + "'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.CenSubmiitTimeTo)))
			{
				where += " AND pur.ComResponseTime < '" + sq.CenSubmiitTimeTo + "'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.OrderName)))
			{
				where += " AND pur.OrderName LIKE '%" + sq.OrderName + "%'";
			}
			if (!string.IsNullOrEmpty(Convert.ToString(sq.HospitalName)))
			{
				where += " AND pur.HospitalName LIKE '%" + sq.HospitalName + "%'";
			}
			if (!string.IsNullOrEmpty(sq.CompanyID_PS))
			{
				where += " AND pur.CompanyID_PS ='" + sq.CompanyID_PS + "'";
			}
			return where;
		}

		/// <summary>
		/// 超过24小时未配送的采购详情
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable Get24HourByPurchase(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strWhere = get24HourWhere(sq);
			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);
			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
			}
			string SQL = string.Format(@"SELECT *
                                             FROM   ( SELECT    pur.OrderDetailID ,
                                                                pur.CompanyID_PS , 
                                                                pur.PurchaseCount ,
                                                                pur.Price , 
                                                                dis.DistributeCount ,
                                                                pur.HospitalID ,
                                                                pur.OrderName , 
                                                                pur.HospitalName ,
                                                                pur.AreaID ,
                                                                pur.AreaName ,
                                                                pur.ProcureCatalogID ,
                                                                pur.GoodsID ,
                                                                pur.MedicineModelName , 
                                                                pur.Factor ,
                                                                pur.Pack , 
                                                                pur.ProcurePrice ,  
                                                                pur.ProductName , 
                                                                pur.Unit ,
                                                                pur.PinyinCode ,  
                                                                pur.CompanyName_SC ,  
                                                                pur.companyName_TB ,
                                                                pur.Outlookc ,
                                                                pur.Material ,
                                                                pur.CompanyName_PS , 
                                                                pur.CenSubmiitTime , 
                                                                pur.ComResponseTime , 
                                                                dis.DistributeTime,
                                                                DATEDIFF(HOUR,ComResponseTime,CASE WHEN dis.DistributeTime IS NULL THEN GETDATE() ELSE dis.DistributeTime END )-24 OutnumberHour,
                                                                hos.IsCountry ,
                                                                hos.Levels ,
                                                                hos.IsGovernment
                                                      FROM      dbo.View_PurchaseOrderDetail pur
                                                                LEFT JOIN dbo.TB_Distribution dis ON pur.OrderDetailID = dis.OrderDetailID
                                                                LEFT JOIN dbo.TB_Hospital hos ON pur.HospitalID = hos.HospitalID
                                                      WHERE     pur.OrderDetailState = 2
                                                                AND DATEDIFF(HOUR, ComResponseTime,
                                                                             CASE WHEN dis.DistributeTime IS NULL
                                                                                  THEN GETDATE()
                                                                                  ELSE dis.DistributeTime
                                                                             END) > 24 {0}
                                                    ) AA
                                             WHERE  1 = 1 ORDER BY {1}", strWhere + hosWhere, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 获取医疗机构计划采购与实际入库的数据
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlan(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPur = "", strPlan = "", strAreaid = "";
			if (!string.IsNullOrEmpty(Convert.ToString(sq.PlanTimeFrom)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PlanTimeFrom);
				strPur = string.Format("AND YEAR(WarehouseTime) = YEAR('{0}') AND MONTH(WarehouseTime) = MONTH('{0}')", sq.PlanTimeFrom);
				sq.PlanTimeFrom = null;
			}
			if (!string.IsNullOrEmpty(sq.AreaID))
			{
				strAreaid = sq.AreaID;
				sq.AreaID = null;
			}

			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);
				sq.Levels = null;
			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);
				sq.IsCountry = null;
			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
				sq.IsGovernment = null;
			}
			if (!string.IsNullOrEmpty(sq.HospitalName))
			{
				hosWhere += string.Format(" AND HospitalName LIKE '%{0}%'", sq.HospitalName);
				sq.HospitalName = null;
			}
			//if (!string.IsNullOrEmpty(sq.SortID))
			//{
			//    hosWhere += string.Format(" and sortid='{0}'", sq.SortID);
			//    sq.SortID = null;
			//}
			string where = DAL_Helper.GetWhereCondition<SuperviseQuery>(sq, false);
			string SQL = string.Format(@" SELECT ROW_NUMBER() OVER( ORDER BY  {4}) xuhao,*
                                         FROM   ( SELECT    prosort.ProductName ,
                                                            prosort.ProcurePrice ,
                                                            prosort.ProcureCatalogID ,
                                                            prosort.Pack ,
                                                            prosort.MedicineModelName ,
                                                            prosort.CompanyName_SC ,
                                                            ISNULL(planCount, 0) planCount ,
                                                            ISNULL(WareCount, 0) WareCount ,
                                                            prosort.sid,
                                                            prosort.sort,
                                                            ISNULL(planCount, 0) - ISNULL(WareCount, 0) OutNumber
                                                  FROM      ( SELECT    *
                                                              FROM      ( SELECT    pro.*,dbo.TB_Sort.SortName sort,dbo.TB_Sort.SortID sid
                                                                          FROM      dbo.TB_ProcureCatalogSort prosort
                                                                                    LEFT JOIN dbo.View_ProcureCatalog_TB pro ON prosort.ProcureCatalogID = pro.ProcureCatalogID
                                                                                    INNER JOIN dbo.TB_Sort ON dbo.TB_Sort.SortID=prosort.SortID
                                                                          WHERE     prosort.SortID IN ( select sortid from tb_sort )
                                                                        ) pp 
                                                              WHERE     1 = 1 {0}
                                                            ) prosort
                                                            LEFT JOIN ( SELECT  ProcureCatalogID ,
                                                                                SUM(PuchaseCount) planCount
                                                                        FROM    dbo.TB_HospitalPurchasePlan
                                                                        WHERE   1 = 1 
                                                                               {1}
                                                                                AND HospitalID IN (
                                                                                SELECT  HospitalID
                                                                                FROM    dbo.TB_Hospital
                                                                                WHERE   1 = 1 {3} )
                                                                        GROUP BY ProcureCatalogID
                                                                      ) hosplan ON prosort.ProcureCatalogID = hosplan.ProcureCatalogID
                                                            LEFT JOIN ( SELECT  ProcureCatalogID ,
                                                                                SUM(WarehouseCount ) WareCount
                                                                        FROM    dbo.TB_Distribution
                                                                        WHERE   OrderDetailState > 0
                                                                                {2}
                                                                                AND HospitalID IN ( SELECT
                                                                                                      HospitalID
                                                                                                    FROM
                                                                                                      dbo.TB_Hospital
                                                                                                    WHERE
                                                                                                      1 = 1 {3})
                                                                        GROUP BY ProcureCatalogID
                                                                      ) pur ON prosort.ProcureCatalogID = pur.ProcureCatalogID
                                                ) AA
                                         WHERE  1 = 1 
                                         ORDER BY {4} ", where, strPlan, strPur, strAreaid + hosWhere, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		///  
		/// </summary>
		/// <param name="vs"></param>
		/// <returns></returns>
		public static string getHospitalPurchasePlanWhere(SuperviseQuery vs)
		{
			string where = "";
			if (!string.IsNullOrEmpty(vs.HospitalID))
			{
				where += string.Format(" AND  HospitalID='{0}'", vs.HospitalID);
			}

			if (!string.IsNullOrEmpty(vs.HospitalName))
			{
				where += string.Format(" AND HospitalName LIKE '%{0}%'", vs.HospitalName);
			}
			if (!string.IsNullOrEmpty(vs.ProductName))
			{
				where += string.Format(" AND ProductName LIKE '%{0}%'", vs.ProductName);

			}
			if (!string.IsNullOrEmpty(vs.PinyinCode))
			{
				where += string.Format(" AND PinyinCode LIKE '%{0}%'", vs.PinyinCode);

			}

			if (!string.IsNullOrEmpty(vs.Pack))
			{
				where += string.Format(" AND Pack LIKE '%{0}%'", vs.Pack);

			}
			if (!string.IsNullOrEmpty(vs.MedicineModelName))
			{
				where += string.Format(" AND MedicineModelName LIKE '%{0}%'", vs.MedicineModelName);
			}
			if (!string.IsNullOrEmpty(vs.CompanyName_TB))
			{
				where += string.Format(" AND CompanyName_TB LIKE '%{0}%'", vs.CompanyName_TB);

			}
			if (!string.IsNullOrEmpty(vs.CompanyName_SC))
			{
				where += string.Format(" AND CompanyName_SC LIKE '%{0}%'", vs.CompanyName_SC);

			}
			if (!string.IsNullOrEmpty(vs.CompanyName_PS))
			{
				where += string.Format("AND CompanyName_PS LIKE '%{0}%'", vs.CompanyName_PS);

			}
			//AND CompanyID_PS ='' AND CompanyID_TB=''
			if (!string.IsNullOrEmpty(vs.CompanyID_PS))
			{
				where += string.Format("AND CompanyID_PS ='{0}'", vs.CompanyID_PS);

			}
			if (!string.IsNullOrEmpty(vs.CompanyID_SC))
			{
				where += string.Format("AND CompanyID_SC ='{0}'", vs.CompanyID_SC);

			}

			if (!string.IsNullOrEmpty(vs.CompanyID_TB))
			{
				where += string.Format("AND CompanyID_TB  ='{0}'", vs.CompanyID_TB);
			}

			if (!string.IsNullOrEmpty(vs.IsGovernment))
			{
				where += string.Format(" AND IsGovernment ='{0}'", vs.IsGovernment);
			}
			if (!string.IsNullOrEmpty(vs.IsEssential))
			{
				where += string.Format(" AND IsEssential ='{0}'", vs.IsEssential);
			}
			//AND OrderName LIKE '%%' AND BatchRecordID ='' AND InvoiceID ='' 

			if (!string.IsNullOrEmpty(vs.OrderName))
			{
				where += string.Format(" AND OrderName LIKE '%{0}%' ", vs.OrderName);
			}
			if (!string.IsNullOrEmpty(vs.BatchRecordID))
			{
				where += string.Format("  AND BatchRecordID ='{0}'", vs.BatchRecordID);
			}
			if (!string.IsNullOrEmpty(vs.InvoiceID))
			{
				where += string.Format("  AND InvoiceID ='{0}'", vs.InvoiceID);
			}


			if (!string.IsNullOrEmpty(vs.IsCountry))
			{

				where += string.Format(" AND IsCountryDetail IN({0})", vs.IsCountry);

			}
			if (!string.IsNullOrEmpty(vs.Levels))
			{

				where += string.Format(" AND Levels IN ({0})", vs.Levels);

			}
			if (!string.IsNullOrEmpty(vs.SortID))
			{
				where += string.Format(" and sortid='{0}'", vs.SortID);
			}
			return where;

		}
		/// <summary>
		/// 获取医疗机构计划采购与实际入库的数据生产企业明细
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanByCompanySC(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPur = "", strPlan = "", strAreaid = "";
			if (!string.IsNullOrEmpty(Convert.ToString(sq.PlanTimeFrom)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PlanTimeFrom);
				strPur = string.Format("AND YEAR(CenSubmiitTime) = YEAR('{0}') AND MONTH(CenSubmiitTime) = MONTH('{0}')", sq.PlanTimeFrom);

			}
			if (!string.IsNullOrEmpty(sq.AreaID))
			{
				strAreaid = sq.AreaID;

			}
			string where = getHospitalPurchasePlanWhere(sq); //DAL_Helper.GetWhereCondition<SuperviseQuery>(sq, false);
			if (!string.IsNullOrEmpty(sq.ProcureCatalogID))
			{
				where += string.Format("AND ProcureCatalogID ='{0}'", sq.ProcureCatalogID);
			}
			string SQL = string.Format(@" 
                                        SELECT  *
                                        FROM    ( SELECT    pro.* ,
                                                            ISNULL(pur.PurchaseCount, 0) PurchaseCount ,
                                                            ISNULL(hosplan.PlanCount, 0) PlanCount,
                                                            ISNULL(pur.PurchaseCount, 0) - ISNULL(hosplan.PlanCount, 0) OutNumber
                                                  FROM      ( SELECT    *
                                                              FROM      dbo.View_ProcureCatalog_TB
                                                              WHERE     1 = 1
                                                                        {0}
                                                            ) pro
                                                            LEFT JOIN ( SELECT  ProcureCatalogID ,
                                                                                SUM(PurchaseCount) PurchaseCount
                                                                        FROM    dbo.TB_PurchaseOrderDetail
                                                                        WHERE   1 = 1
                                                                                AND OrderDetailState > 0
                                                                                {2} 
                                                                                {3} 
                                                                        GROUP BY ProcureCatalogID
                                                                      ) pur ON pro.ProcureCatalogID = pur.ProcureCatalogID
                                                            LEFT JOIN ( SELECT  ProcureCatalogID ,
                                                                                SUM(PuchaseCount) PlanCount
                                                                        FROM    dbo.TB_HospitalPurchasePlan
                                                                        WHERE   1 = 1
                                                                                {1}
                                                                                AND HospitalID IN (
                                                                                SELECT  HospitalID
                                                                                FROM    dbo.TB_Hospital
                                                                                WHERE   1=1 {3} )
                                                                        GROUP BY ProcureCatalogID
                                                                      ) hosplan ON pro.ProcureCatalogID = hosplan.ProcureCatalogID
                                                ) AA
                                        WHERE   1 = 1    
                                        ORDER BY {4}", where, strPlan, strPur, strAreaid, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}

		/// <summary>
		/// 获取医疗机构计划采购与实际入库的数据医疗机构明细
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanByHospital(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPur = "", strPlan = "";
			if (!string.IsNullOrEmpty(Convert.ToString(sq.PlanTimeFrom)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PlanTimeFrom);
				strPur = string.Format("AND YEAR(WarehouseTime) = YEAR('{0}') AND MONTH(WarehouseTime) = MONTH('{0}')", sq.PlanTimeFrom);

			}



			string hosWhere = string.Empty;
			if (!string.IsNullOrEmpty(sq.Levels))
			{
				hosWhere += string.Format(" AND Levels IN ({0})", sq.Levels);

			}
			if (!string.IsNullOrEmpty(sq.IsCountry))
			{
				hosWhere += string.Format(" AND IsCountry IN ({0})", sq.IsCountry);

			}

			if (!string.IsNullOrEmpty(sq.IsGovernment))
			{
				hosWhere += string.Format(" AND IsGovernment IN ({0})", sq.IsGovernment);
			}

			if (!string.IsNullOrEmpty(sq.AreaID))
			{
				hosWhere += sq.AreaID;
			}
			if (!string.IsNullOrEmpty(sq.HospitalName))
			{
				hosWhere += string.Format(" AND HospitalName LIKE '%{0}%'", sq.HospitalName);

			}
			//string where =getHospitalPurchasePlanWhere(sq); //DAL_Helper.GetWhereCondition<SuperviseQuery>(sq, false); 
			string SQL = string.Format(@" 
                                        SELECT  CASE WHEN hosplan.HospitalName IS NULL THEN ware.HospitalName
                                                     ELSE hosplan.HospitalName
                                                END HospitalName ,
                                                ISNULL(hosPlan.planCount, 0) planCount ,
                                                ISNULL(ware.WareCount, 0) WareCount ,
                                                ISNULL(hosPlan.planCount, 0) - ISNULL(ware.WareCount, 0) OutNumber
                                        FROM    ( SELECT    *
                                                  FROM      ( SELECT    SUM(PuchaseCount) planCount ,
                                                                        hos.HospitalID ,
                                                                        ProcureCatalogID ,
                                                                        hos.HospitalName ,
                                                                        MAX(IsCountry) IsCountry ,
                                                                        MAX(IsGovernment) IsGovernment ,
                                                                        MAX(Levels) Levels ,
                                                                        MAX(hos.AreaID) AreaID
                                                              FROM      dbo.TB_HospitalPurchasePlan hosplan
                                                                        LEFT JOIN dbo.TB_Hospital hos ON hosPlan.HospitalID = hos.HospitalID
                                                              WHERE     1 = 1
                                                                        {0}
                                                              GROUP BY  hos.HospitalID ,
                                                                        hos.HospitalName ,
                                                                        ProcureCatalogID
                                                            ) AA
                                                  WHERE     1 = 1
                                                            AND ProcureCatalogID = '{1}'
                                                            {2}
                                                ) hosplan
                                                FULL OUTER  JOIN ( SELECT   HospitalID ,
                                                                            HospitalName ,
                                                                            SUM(WarehouseCount) WareCount
                                                                   FROM     dbo.TB_Distribution
                                                                   WHERE    OrderDetailState > 0
                                                                            AND ProcureCatalogID = '{1}'
                                                                            {3}
                                                                            AND HospitalID IN (
                                                                            SELECT  HospitalID
                                                                            FROM    dbo.TB_Hospital
                                                                            WHERE   1 = 1
                                                                                    {2} )
                                                                   GROUP BY HospitalID ,
                                                                            HospitalName
                                                                 ) ware ON hosplan.HospitalID = ware.HospitalID   
                                                                                WHERE   1 = 1    
                                                                                ORDER BY {4}", strPlan, sq.ProcureCatalogID, hosWhere, strPur, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}



		/// <summary>
		/// 正常填报采购计划的医院列表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanListByNormal(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPlan = "", strAreaID = "";
			strAreaID = sq.AreaID;
			sq.AreaID = null;
			if (!string.IsNullOrEmpty(Convert.ToString(sq.PurChaseTime)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PurChaseTime);

			}

			string where = getHospitalPurchasePlanWhere(sq);
			string SQL = string.Format(@" 
                                        SELECT  *, ROW_NUMBER() OVER ( ORDER BY HospitalID ) AS rowNO
                                        FROM    ( SELECT    hos.HospitalID ,hos.hospitalname,
                                                            SUM(PuchaseCount) planCount,
                                                            MAX(hos.IsCountry) IsCountry ,
                                                            MAX(hos.IsCountryDetail) IsCountryDetail ,
                                                            MAX(hos.IsGovernment) IsGovernment,
                                                            MAX(hos.Levels) Levels
                                                  FROM      dbo.TB_HospitalPurchasePlan hosplan
                                                            LEFT JOIN dbo.TB_Hospital hos ON hosplan.HospitalID = hos.HospitalID
                                                  WHERE     1=1 {0} {1} {2}
                                                  GROUP BY  hos.HospitalID,hos.hospitalname
                                                ) AA
                                        WHERE   planCount > 0
                                        ORDER BY {3}", where, strPlan, strAreaID, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 正常填报采购计划的医院列表详情
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanListByNormalDetail(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPlan = "", strAreaID = "";
			strAreaID = sq.AreaID;

			if (!string.IsNullOrEmpty(Convert.ToString(sq.PurChaseTime)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PurChaseTime);
				sq.PurChaseTime = null;
			}
			sq.IsCountry = null;
			sq.IsGovernment = null;
			sq.Levels = null;
			string where = getHospitalPurchasePlanWhere(sq);
			if (!string.IsNullOrEmpty(sq.ProcureCatalogID))
			{
				where += " AND ProcureCatalogID ='" + sq.ProcureCatalogID + "'";
			}
			string SQL = string.Format(@" 
                                         SELECT    *
                                          FROM      ( SELECT    hosplan.* ,
                                                                ISNULL(dis.WarehouseCount, 0) WarehouseCount,
                                                                ISNULL(hosplan.planCount,0)-ISNULL(dis.WarehouseCount, 0) OutNumber
                                                      FROM      ( SELECT    pro.ProcureCatalogID ,
                                                                            pro.ProductName ,
                                                                            pro.MedicineModelName ,
                                                                            pro.Outlookc ,
                                                                            pro.Pack ,
                                                                            pro.Unit ,
                                                                            pro.PinyinCode ,
                                                                            pro.CompanyName_SC ,
                                                                            pro.ProcurePrice ,
                                                                            hos.HospitalName ,
                                                                            hosplan.PuchaseCount planCount ,
                                                                            hosplan.PurChaseTime ,
                                                                            YEAR(hosplan.PurChaseTime) purYear ,
                                                                            MONTH(hosplan.PurChaseTime) purMonth ,
                                                                            dbo.TB_Sort.SortID,
                                                                           dbo.TB_Sort.SortName,
                                                                            hosplan.HospitalID
                                                                  FROM      dbo.TB_HospitalPurchasePlan hosplan
                                                                            LEFT JOIN dbo.View_ProcureCatalog_TB pro ON hosplan.ProcureCatalogID = pro.ProcureCatalogID
                                                                            LEFT JOIN dbo.TB_Hospital hos ON hosplan.HospitalID = hos.HospitalID
                                                                            left JOIN TB_ProcureCatalogSort pos ON pos.ProcureCatalogID=pro.ProcureCatalogID
                                                                            left JOIN   dbo.TB_Sort ON dbo.TB_Sort.SortID = pos.SortID
                                                                ) hosplan
                                                                LEFT JOIN ( SELECT  HospitalID ,
                                                                                    HospitalName ,
                                                                                    ProcureCatalogID ,
                                                                                    YEAR(WarehouseTime) wareYear ,
                                                                                    MONTH(WarehouseTime) wareMonth ,
                                                                                    SUM(WarehouseCount) WarehouseCount
                                                                            FROM    dbo.TB_Distribution
                                                                            WHERE   OrderDetailState > 0
                                                                            GROUP BY HospitalID ,
                                                                                    HospitalName ,
                                                                                    ProcureCatalogID ,
                                                                                    YEAR(WarehouseTime) ,
                                                                                    MONTH(WarehouseTime)
                                                                          ) dis ON hosplan.HospitalID = dis.HospitalID
                                                                                   AND hosplan.ProcureCatalogID = dis.ProcureCatalogID
                                                                                   AND hosplan.purYear = dis.wareYear
                                                                                   AND hosplan.purMonth = dis.wareMonth
                                                    ) AA
                                        WHERE 1=1 {0} {1}
                                        ORDER BY {2}", where, strPlan, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 采购计划填报为0的医院列表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanListByZero(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPlan = "", strAreaID = "";
			strAreaID = sq.AreaID;

			if (!string.IsNullOrEmpty(Convert.ToString(sq.PurChaseTime)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PurChaseTime);

			}

			string where = getHospitalPurchasePlanWhere(sq);
			string SQL = string.Format(@" 
                                        SELECT  *, ROW_NUMBER() OVER ( ORDER BY HospitalID ) AS rowNO
                                        FROM    ( SELECT    hos.HospitalID ,hos.hospitalname,
                                                            SUM(PuchaseCount) planCount,
                                                            MAX(hos.IsCountry) IsCountry ,
                                                            MAX(hos.IsCountryDetail) IsCountryDetail ,
                                                            MAX(hos.IsGovernment) IsGovernment,
                                                            MAX(hos.Levels) Levels 
                                                  FROM      dbo.TB_HospitalPurchasePlan hosplan
                                                            LEFT JOIN dbo.TB_Hospital hos ON hosplan.HospitalID = hos.HospitalID
                                                  WHERE     1=1 {0} {1} {2}
                                                  GROUP BY  hos.HospitalID,hos.hospitalname
                                                ) AA
                                        WHERE   planCount = 0
                                        ORDER BY {3}", where, strPlan, strAreaID, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}



		/// <summary>
		/// 没有填报采购计划的医院列表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetHospitalPurchasePlanListByNone(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
		{
			string strPlan = "", strAreaID = "";
			strAreaID = sq.AreaID;

			if (!string.IsNullOrEmpty(Convert.ToString(sq.PurChaseTime)))
			{
				strPlan = string.Format("AND YEAR(PurChaseTime) = YEAR('{0}') AND MONTH(PurChaseTime) = MONTH('{0}')", sq.PurChaseTime);

			}
			string where = getHospitalPurchasePlanWhere(sq);
			string SQL = string.Format(@" 
                                        SELECT  HospitalID ,
                                                HospitalName,ROW_NUMBER() OVER ( ORDER BY HospitalID ) AS rowNO, '0' planCount
                                        FROM    dbo.TB_Hospital
                                        WHERE   HospitalID NOT IN ( SELECT  HospitalID
                                                                    FROM    dbo.TB_HospitalPurchasePlan
                                                                    WHERE   1 = 1
                                                                            {0})
                                                {1}{2}
                                        ORDER BY {3}", strPlan, where, strAreaID, sq.SortFields);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(SQL, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;
			return des.GetSpecailPage();
		}


		/// <summary>
		/// 药品流水表
		/// </summary>
		/// <param name="sq"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetYiBaoList(SuperviseQuery sq)
		{
			string where = HZ.Data.DAL_Helper.GetWhereCondition(sq, false);
			string SQL = string.Format(@"SELECT * FROM dbo.TB_YiBao WHERE 1=1 {0}", where);
			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(SQL);
			return dt;
		}

		/// <summary>
		///  获取药品信息
		/// </summary>
		/// <param name="ProcureCatalogID"></param>
		/// <returns></returns>
		public static HZ.Supervise.Model.View.ProcureCatalog GetProcureCatalogByID(string ProcureCatalogID)
		{
			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format("SELECT * FROM View_ProcureCatalog_TB WHERE ProcureCatalogID = '{0}'", ProcureCatalogID));
			if (dt.Rows.Count > 0)
			{
				return DAL_Helper.CommonFill<HZ.Supervise.Model.View.ProcureCatalog>(dt.Rows[0]);
			}
			else
			{
				throw new Exception("未查到该药品");
			}
		}


	}
}
